egerla
06-08-2016, 03:39 AM
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.
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.