VBA code to put the formula in to first empty column of third row

[Wafee]

Can comeone help me with below code.

I have this formula - =IF($A4<> "",VLOOKUP($A4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AO$2,0),0),VLOOKUP($B4,Pivot!A:AZ,MATCH("Grand Total",Pivot!$A$2:$AO$2,0),0)) which needs to be entered in sheet1 of my workbook. it has be enetered in the 3rd row of the first empty column where there there wont be any data(except in first two rows).

for example in my case G column is empty except it will have data in G1 & G2. when code looks for an empty column G will be the first one and the formula needs to be entered in G3.

 

[Peter_SSs]

You're welcome. Glad it helped.

 

[Wafee]

Rather than enter one formula then copy/drag down, you can put them all in at once.

Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(Range("B" & Rows.Count).End(xlUp).Row - 2).Formula = _
  "=IF($A4<> """",VLOOKUP($A4,Pivot!A:AZ,MATCH(""Grand Total"",Pivot!$A$2:$AO$2,0),0),VLOOKUP($B4,Pivot!A:AZ,MATCH(""Grand Total"",Pivot!$A$2:$AO$2,0),0))"

Hi peter,

I promise, it would be the last query. rather than finding the which column to fill i have the column (stored in "Fcol") and I want to have the above formula pasted from 4th row of the column. thank you in advance.

 

[Peter_SSs]

i have the column (stored in "Fcol") and I want to have the above formula pasted from 4th row of the column.

That would be like

Cells(4, Fcol).Resize(Range("B" & Rows.Count).End(xlUp).Row - 3).Formula = ......

Or if you wanted to make it easier to amend, declare startrow as Long and use something like this instead.

startrow = 4
Cells(startrow, Fcol).Resize(Range("B" & Rows.Count).End(xlUp).Row - startrow + 1).Formula = ....

 

[Wafee]

That would be like

Cells(4, Fcol).Resize(Range("B" & Rows.Count).End(xlUp).Row - 3).Formula = ......

Or if you wanted to make it easier to amend, declare startrow as Long and use something like this instead.

startrow = 4
Cells(startrow, Fcol).Resize(Range("B" & Rows.Count).End(xlUp).Row - startrow + 1).Formula = ....

Thak you Peter. ??

 

[Peter_SSs]

No problem - glad to help.