VLOOKUP formula with subtotals

[egerla]

Hi,

I used this code to automate VLOOKUP formula in my Excel file, but I must "pick up" subtotal values in VLOOKUP. How to do that.

This is code:

On Error Resume Next
Dim Ulaz_Row As Long
Dim Ulaz_Clm As Long
Dim Izlaz_Row As Long
Dim Izlaz_Clm As Long


ctr = 0
Table1 = Sheet3.Range("VLKPRange") ' PartNo Column from PocSta table
Table2 = Sheet1.Range("UlazRange") ' Range of Ulaz Table 1
Table3 = Sheet2.Range("IzlazRange") ' Range od Izlaz Table 1
Ulaz_Row = Sheet3.Range("C2").Row ' Change E3 with the cell from where you need to start populating the Ulaz
Ulaz_Clm = Sheet3.Range("C2").Column
Izlaz_Row = Sheet3.Range("D2").Row ' Change E3 with the cell from where you need to start populating the Izlaz
Izlaz_Clm = Sheet3.Range("D2").Column
For Each cl In Table1
Sheet3.Cells(Ulaz_Row, Ulaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-2],Ulaz!R2C1:R10000C4, 2, False)"
Ulaz_Row = Ulaz_Row + 1
ctr = ctr + 1
Next cl
For Each cl In Table1
Sheet3.Cells(Izlaz_Row, Izlaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3],Izlaz!R2C1:R10000C4, 2, False)"
Izlaz_Row = Izlaz_Row + 1
ctr = ctr + 1
Next cl


MsgBox "Done"



End Sub

I tried something like this: Sheet3.Cells(Izlaz_Row, Izlaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3]&" Total",Izlaz!R2C1:R10000C4, 2, False)" but it does not work. Can anybody help me?

Thanks.

[offthelip]

A quick easy way to do this is to add an extra column with formula "=A2 & " Total" and use that as the lookup variable.
An alternative way to do is by changiung your code to the following:

lookupvalue = Chr(34) & Sheet3.Cells(Izlaz_Row, Izlaz_Clm - 3) & " Total" & Chr(34)
Sheet3.Cells(Izlaz_Row, Izlaz_Clm) = "=VLOOKUP(" & lookupvalue & ",Izlaz!$A$2:$D$10000, 2, False)"




Note this does make the lookup an absolute value so may not totally meet your requirements
Note: I seemed to have a problem with the row column addressing so I changed it to absolute.

[egerla]

This is the solution. Thanks a lot for helping.



A quick easy way to do this is to add an extra column with formula "=A2 & " Total" and use that as the lookup variable.
An alternative way to do is by changiung your code to the following:

lookupvalue = Chr(34) & Sheet3.Cells(Izlaz_Row, Izlaz_Clm - 3) & " Total" & Chr(34)
Sheet3.Cells(Izlaz_Row, Izlaz_Clm) = "=VLOOKUP(" & lookupvalue & ",Izlaz!$A$2:$D$10000, 2, False)"




Note this does make the lookup an absolute value so may not totally meet your requirements
Note: I seemed to have a problem with the row column addressing so I changed it to absolute.