Paste formula in a particular column

[siddharthnk]

Hi Readers,

My problem arises from the fact that I run macros on a report where the columns keep changing places. I need a macro which can

1. Locate a particular column called PACE 1
2. Insert a formula from the first row under the PACE 1 column
3. The formula needs to be such that
   1. If there is a value (number) in any of the columns i.e. P, A, C Or E, it inserts the column header into the corresponding row under the PACE 1 column
   2. If there are values in two or more columns of the 4 columns, it needs to consider the highest value and insert the respective column header in the corresponding row in the PACE 1 column
   3. If there is a zero in one of the four columns i.e. P,A,C, Or E, it needs to insert the respective column header in the corresponding row in the PACE 1 column

Now the real problem is that the column headers are dynamic so the person preparing the report may insert new columns, delete columns or shuffle the location of the columns. The macro needs to ignore the location of the columns and function purely on the basis of the column headers.

I need another macro which is a slightly modified version of the first macro and is used in a second column PACE 2. The criteria for the first macro remain the same as in the first macro, however in the second macro, wherever column headers P or E are being inserted in column PACE 2 instead of the column headers I need the value P&E to be inserted.

In both the PACE 1 and PACE 2 columns, the formula needs to be inserted in every row for which there is a value in the 1st column of the table.


Any help will be greatly appreciated

 

[antm79]

Hi

can you post a before and after example?

 

[siddharthnk]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Age[/TD]
[TD]P[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]PACE 1[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P
[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]

 

[antm79]

HI,

Are the column headers always constant and unique? Could the columns inserted by the user duplicate the column headers P,A,C,E? what verison of excel will this be run on?

Thanks,
Antm79

 

[siddharthnk]

The columns are unique. The user can insert other columns and thereby change the location of these columns but the inserted columns will not be duplicates of P,A,C & E. Excel version is 2007.

 

[antm79]

Hi,

Maybe you don't need to go down the VBA route, can you not just use absolute referencing and excel formulas?

Try this formula to answer your first marco question:

=INDEX($D$1:$G$1,MATCH(MAX($D3:$G3),$D3:$G3,0))

substitute $D$1:$G$1 with the P,A,C and E headers on your sheet and the $D3:$G3),$D3:$G3 with the first row of data you wish to process. IF you then insert extra columns It shouldn't matter as you have used absolute referencing for the column headers.

Hope this helps