VBA Macro for pasting formula in a particular column

[Siddhart Nagwekar]

Hi Readers,


First off, thanks for accepting me into the Chandoo community.

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

Locate a particular column called PACE 1

1. Insert a formula from the first row under the PACE 1 column
2. 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 have attached an excel workbook with an example of all the above criteria that I have mentioned above.

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

 

[vijay.vizzu]

As per your information.. the change may happen in Columns (insert new columns, delete columns or shuffle the location of the columns) not in Rows. So is there any starting point ? like as per your attached file the starting position is B2. Next question is, Is your data will be in continuous? like inserted a blank column in between a columns. Column Headers will be constant or may changes as per the user requirement ?

 

[Siddhart Nagwekar]

As per your information.. the change may happen in Columns (insert new columns, delete columns or shuffle the location of the columns) not in Rows. So is there any starting point ? like as per your attached file the starting position is B2. Next question is, Is your data will be in continuous? like inserted a blank column in between a columns. Column Headers will be constant or may changes as per the user requirement ?

Yes the change will happen only in columns and not in rows.
the starting point i.e. first row is A2 onwards, column headers are from A1
Data is continuous
Columns headers may not be constant i.e. new columns might be inserted in the table (This is the very reason I am facing this problem)

 

[Siddhart Nagwekar]

As per your information.. the change may happen in Columns (insert new columns, delete columns or shuffle the location of the columns) not in Rows. So is there any starting point ? like as per your attached file the starting position is B2. Next question is, Is your data will be in continuous? like inserted a blank column in between a columns. Column Headers will be constant or may changes as per the user requirement ?

Hi Vijay.vizzu,

any luck with this yet?

Thanks and Regards

 

[NARAYANK991]

Hi Nagwekar ,

You have given 3 rules out of which rule 1 and rule 2 are mutually exclusive ; however , rule 3 and rule 2 can both be applicable ; in such a case will the priority be the way you have listed the rules i.e. will the column header of the highest value appear , or will the column header of the column having the zero appear ?

Will there be 2 columns having the same maximum value , or 2 columns having 0 ? If yes , what should be done in these cases ?

Narayan

 

[Siddhart Nagwekar]

The third scenario will just have a zero in any one of the four columns. So no other numbers will be present. If 2 columns have the same maximum value, they will be columns named "P" & and column "E". Its highly unlikely that such a situation will occur with "A" and "C". When it does occur with "P" & "E", then the column name "P" should be considered.