Vlookup vba code

  • Hi All,


    I want to update this formula through vba code..


    Formula:

    =VLOOKUP(VLOOKUP(.Cells(i,6),[Macrobook_V_0.3.6.xlsm]DB_list!$A:$B,2,0),'[Input.xlsx]FREETIME OVERVIEW'!$B:$F,MATCH(VLOOKUP(H2,[Macrobook_V_0.3.6.xlsm]Containers!$A:$B,2,0),'[Input.xlsx]FREETIME OVERVIEW'!$B$10:$F$10,0),0)


    So I wrote this down but its not working. Can anyone please explain what I'm missing in this code.


    Code
    1. Dim r As Range, r2 As Range, r3 As Range
    2. Set r = wb3.Sheets(5).[b10].CurrentRegion
    3. Set r2 = ThisWorkbook.Sheets("DB_List").Cells(1).CurrentRegion
    4. Set r3 = ThisWorkbook.Sheets("Containers").Cells(1).CurrentRegion
    5. = Application.VLOOKUP(Application.VLOOKUP(.Cells(i, 6), r2, 2, 0), r2, Application.MATCH(Application.VLOOKUP(.Cells(i, 8), r3, 2, 0), r, 0), 0)
  • That's not a valid formula since you have VBA code in the middle of it, and your code doesn't make sense because you aren't using a formula anywhere, plus it is not clear what should be on the left hand side of that = sign.


    Maybe just explain what you want to put on the sheet (formula or values) and where it should go.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Not surprised you were confused by this question, Rory!


    Hanes was trying to adapt a bit of VBA I had written for him to suit a couple of changes in the VLOOKUP tables.


    The solution he was looking for was:

    Code
    1. For i = 2 To .Cells(.Rows.Count, 7).End(xlUp).Row
    2. .Cells(i, lCol) = _
    3. Application.VLookup(Application.VLookup(.Cells(i, 6), r2, 2, 0), r, _
    4. Application.Match(Application.VLookup(.Cells(i, 8), r3, 2, 0), r.Rows(1), 0), 0)
    5. Next

    where r, r2 & r3 are Range variables set to 3 different tables in 3 different workbooks. The results are placed in a 4th workbook.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • :) That makes more sense

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why