big job 2

[RET79]

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

[RET79]

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.