big job 2

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top