Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: big job 2

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok.

    I have 3 columns like this

    YV VA NUM
    1 0 2.3
    3 0 4.3
    99 0 4.5
    101 0 3.4
    -23 0 4.2
    0 1 42
    0 -2 46
    0 99 67
    0 102 65

    Now, I want to create some new additional columns based on these one. For instance could have

    YV VA NUM (VA>100,NUM) (YV<0,NUM)
    1 0 2.3 0 0
    3 0 4.3 0 0
    99 0 4.5 0 0
    101 0 3.4 0 0
    -23 0 4.2 0 4.2
    0 1 42 0 0
    0 -2 46 0 0
    0 99 67 0 0
    0 102 65 65 0

    These new 2 columns can be achieved with simple IF functions.

    However, in practice, I would have to create more than 2 calculated columns, perhaps even 10 to 20 of them. This gets laborious. I want to get a macro that adds these columns , with the formulas automatically. Perhaps have some sort of input sheet where I tell the macro what formulas to use. But, I am not sure how to do this.

    What I would like is for the macro to search across the column names until it reaches NUM. Then, insert how many columns of calculated cells I specified and then put the formulas in automatocally.

    Eventually I want to throw all this into a pivot table by the way.

    Hope this makes some sense,

    RET79

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For instance, on some input sheet I could have this.

    NUM
    VA>100
    YV<0

    The macro would then search the column heading on the data sheet until it found NUM. Then it would pick the next column along, insert 2 columns and put in the formulas for VA>100 and YV<0 in them respectively.

    Perhaps the macro could identify which column YV and VA are in order to know which cells to refer to in the IF function.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •