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]

for example in my case G column is empty except it will have data in G1 & G2.

If G1 & G2 have data and G3 is empty, could H1 & H2 also have data?
Or can we assume that the last column with data in row 1 is the column to put the formula in?

Another way to possibly look at it: Is the column to use the column immediately after the last column in row 3 that contains data?

 

[Wafee]

If G1 & G2 have data and G3 is empty, could H1 & H2 also have data?
Or can we assume that the last column with data in row 1 is the column to put the formula in?

Another way to possibly look at it: Is the column to use the column immediately after the last column in row 3 that contains data?

Yes H1 and H2 will also have data. Yes, column to used is the column immediately after the last column in row 3 that contains data and that also should definitely be after column "I".

 

[Peter_SSs]

Yes, column to used is the column immediately after the last column in row 3 that contains data

In that case try

Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).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))"

 

[Wafee]

In that case try

Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).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))"

Thats perfect, thank you mate

 

[Wafee]

In that case try

Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).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, Can you also help me to to drag the formula in the column where it is put untill last row of data as in column "B"?

 

[Peter_SSs]

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))"

 

[Wafee]

In that case try

Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).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 had to change the formula (as in the below) but it is throwing a syntax error. if it is with the above formula it is working fine. can you please help me with this.

ActiveWorkbook.Sheets("View 1 Week on Week Trend - Un").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).Resize(Range("B" & Rows.Count).End(xlUp).Row - 2).Formula = _
"=IF($A4 = "Grand Total",SUM('Source Data'!$M:$M),IF($B4="",SUMIFS('Source Data'!$M:$M,'Source Data'!$T:$T,'View 1 Week on Week Trend - Un'!$A4),SUMIFS('Source Data'!$M:$M,'Source Data'!$T:$T,'View 1 Week on Week Trend - Un'!$A4,'Source Data'!$U:$U,'View 1 Week on Week Trend - Un'!$B4)))"

 

[Peter_SSs]

I have not studied that formula in detail, but when you are inserting a formula with vba you have to double-up any quote marks in the formula itself. So for a start I would try

ActiveWorkbook.Sheets("View 1 Week on Week Trend - Un").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).Resize(Range("B" & Rows.Count).End(xlUp).Row - 2).Formula = _
"=IF($A4 = ""Grand Total"",SUM('Source Data'!$M:$M),IF($B4="""",SUMIFS('Source Data'!$M:$M,'Source Data'!$T:$T,'View 1 Week on Week Trend - Un'!$A4),SUMIFS('Source Data'!$M:$M,'Source Data'!$T:$T,'View 1 Week on Week Trend - Un'!$A4,'Source Data'!$U:$U,'View 1 Week on Week Trend - Un'!$B4)))"

 

[Wafee]

I have not studied that formula in detail, but when you are inserting a formula with vba you have to double-up any quote marks in the formula itself. So for a start I would try

ActiveWorkbook.Sheets("View 1 Week on Week Trend - Un").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).Resize(Range("B" & Rows.Count).End(xlUp).Row - 2).Formula = _
"=IF($A4 = ""Grand Total"",SUM('Source Data'!$M:$M),IF($B4="""",SUMIFS('Source Data'!$M:$M,'Source Data'!$T:$T,'View 1 Week on Week Trend - Un'!$A4),SUMIFS('Source Data'!$M:$M,'Source Data'!$T:$T,'View 1 Week on Week Trend - Un'!$A4,'Source Data'!$U:$U,'View 1 Week on Week Trend - Un'!$B4)))"

that worked, thank you so much Peter