Using VLookup in VBA - Help please

[tommill52]

Hi, VBA novice here.

I have two worksheets (T2 and PalletQty). The data in T2 is generated from a number of other worksheets in a grid format - all this works OK. The number of columns is known but the number of rows will vary. A number of operations are carried out on the rows, starting at row 3, iteratively, to the last row. All but the VLookup function (the last line) work OK.

The objective is, working on the current row specified by variable 'iRow', to look up the part in column A of 'T2', find that part in column 'A' of worksheet 'PalletQty', read the value in column B of 'PalletQty', and store it in column AG of worksheet 'T2'.

The coding is as follows (sorry, I couldn't see a box for loading code into so I have embedded it here).

Many thanks

With Application.WorksheetFunction

Sheets("T2").Activate

For iRow = 3 To LastRow + 1
     
     Worksheets("T2").Cells(iRow, "M").FormulaR1C1 = "=SUM(RC[1]:RC[6])"   
     Worksheets("T2").Cells(iRow, "AI").FormulaR1C1 = "=SUM(RC[-26]:RC[-23])"
     Worksheets("T2").Cells(iRow, "AJ").FormulaR1C1 = "=(SUM(RC[-27]:RC[-24]))+RC[-21]"

     Worksheets("T2").Range("AG" & iRow) "=VLOOKUP(A2,PalletQty!$A$2:$C$150,2,False)"
         
     Next iRow
  
  End With

 

[jscranton]

Worksheets("T2").Range("AG" & iRow).FormulaR1C1 = "=VLOOKUP(A2,PalletQty!$A$2:$C$150,2,False)"

That didn't work?

 

[AlphaFrog]

Try this...

Worksheets("T2").Range("AG" & iRow) = "=VLOOKUP(A[B]" & iRow & "[/B],PalletQty!$A$2:$C$150,2,False)"

Or this to replace the loop

    Sheets("T2").Activate
    
    [color=darkblue]With[/color] Rows("3:" & LastRow + 1)
     
     .Columns("M").FormulaR1C1 = "=SUM(RC[1]:RC[6])"
     .Columns("AI").FormulaR1C1 = "=SUM(RC[-26]:RC[-23])"
     .Columns("AJ").FormulaR1C1 = "=(SUM(RC[-27]:RC[-24]))+RC[-21]"
    
     .Columns("AG").Formula = "=VLOOKUP(A3,PalletQty!$A$2:$C$150,2,False)"
        
  [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[AlphaFrog]

Deleted

 

[tommill52]

HI, Sorry, I am not sure I understand your question.

 

[tommill52]

Hi, I don't know why your code is showing as deleted - I don't think I did anything! Fortunately, I copied the one liner and it worked. Thank you very much for your help. Regards.

 

[AlphaFrog]

Hi, I don't know why your code is showing as deleted - I don't think I did anything! Fortunately, I copied the one liner and it worked. Thank you very much for your help. Regards.

You're welcome.

I accidentally posted twice and then deleted the 2nd one.