1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA Macro Help

Discussion in 'Business Applications' started by ns81, Apr 21, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    Hello,

    I'm looking for some help with a macro I am working on in Excel. So far my macro leaves me with the data on sheet 1 of the attached book1.xls file. What I would like the data to look like after the macro is run is on Sheet 2.

    It's difficult to explain in words but I am hoping someone with experience will see this as pretty simple. I'll try to give a representation below of what I am looking for so you don't have to look at the attached file to get an idea of what I need. The changes I need the macro to make are highlighted in red on sheet 2.

    Sheet 1:


    100 | 328
    100 | 328
    100 | 328
    100 | 328
    95 | 95
    45 | 45
    100 | 245
    100 | 245
    100 | 245

    Sheet 2

    100 | 328
    100 | 328
    100 | 328
    28 | 328
    95 | 95
    45 | 45
    100 | 245
    100 | 245
    45 | 245

    Any help is greatly appreciated!
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Maybe I am reading you wrong but I don't understand what you want. :(
    I cannot make heads or tails form explanation.
    What does the 28 | 328 represent? the 45 | 245 I don't see any relationship to the data above or below.
    Sorry :confused:
     
  3. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    Sorry, I knew I was explaining this poorly. The “|” character represents a column split. So there are two columns in my example. When the column B has a value over 100 (like 328) then in column A I would like the last 100 value in the series of 328 changed to the last 2 numbers of the cell to the right of it. That’s why the last 100 in the 245 series should change to 45. Does that make any more sense or did I confuse the issue more? Thanks for looking at this.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    No that makes sense, I dit get the meaning of the | but the numbers where putting me on the woirng track.
    I'll do some simple tests and mail you the results asap
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    See the attached sheet, there is a button in Sheet1
    I stored your sample result in Sheet3

    Sheet2 will be updated with what is in Sheet1

    The code is written for column F to contaain the first value and G the second one, if you alter the layout you will have to edit the vba code too

    I dit not sort Sheet1 first and assume that column F is alsways sorted and column G too.

    The thing is that I did not test on an a single entry so 55 and 99 are red too
    The value foor the 100|200 is 00

    See what you can do with it and if it helps you.:)
     

    Attached Files:

  6. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    Thank you very much!

    I should have noted that the red color was more to indicate the changes in my example then to be a part of the final code, but the color is irrelevant since the data will be saved out as a csv file.

    I really appreciate your time, I had a vague idea of the logic involved but no way I could have noodled through the code myself.
     
  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    It may be that Ițm wrong by I sow in your data the following rule:
    In column G you have the total sum of something (maybe related to other columns in the worksheet), in column F you need to have the same sum, but the may sum for each row is 100, so you need to have N\ 100 rows with 100, and 1 row which will contain the N mod 100 value, but in case the 100 divides exactly by 100, then you will have only N \ 100 rows with 100 in column F.

    If I'm right then in the case of N=200 (rows 16 and 17), in row 17 you need to have 100, and not 0.

    I could be wrong, in this case just ignore my post.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918424

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice