Excel VLOOKUP() as VBA code

Thank you for looking at my question,

I have a workbook that contains two worksheets (Analysis and Inventory) that are populated dynamically on open, copying data from two other workbooks

I want to replicate a VLOOKUP in VBA so that the sheet carries out the necessary calculations on opening as well.

The VLOOKUP is:

=If(Len(B3)>1,IF(ISNA(VLOOKUP(TEXT(B3,"@"),Inventory!r$A$1:$D$6548,2,0)),"NULL",VLOOKUP(TEXT(B3,"@"),Inventory!r$A$1:$D$6548,2,0)),
IF(LEN(D3)>1,IF(ISNA(VLOOKUP(TEXT(D3,"@"),Inventory!r$A$1:$D$6548,2,0)),"NULL",VLOOKUP(TEXT(D3,"@"),Inventory!r$A$1:$D$6548,2,0)),
IF(LEN(F3)>1,IF(ISNA(VLOOKUP(TEXT(F3,"@"),Inventory!r$A$1:$D$6548,2,0)),"NULL",VLOOKUP(TEXT(F3,"@"),Inventory!r$A$1:$D$6548,2,0)),
IF(LEN(H3)>1,IF(ISNA(VLOOKUP(TEXT(H3,"@"),Inventory!r$A$1:$D$6548,2,0)),"NULL",VLOOKUP(TEXT(H3,"@"),Inventory!r$A$1:$D$6548,2,0)),
IF(LEN(J3)>1,IF(ISNA(VLOOKUP(TEXT(J3,"@"),Inventory!r$A$1:$D$6548,2,0)),"NULL",VLOOKUP(TEXT(J3,"@"),Inventory!r$A$1:$D$6548,2,0)))))))

Because both datasets may not be the same length the next time the sheet is opened I need write this VLOOKUP to Analysis.R3

AnalysisS3 needs to contain the same vlookup but with the LEN() functions going from B3 etc to B4 and Analysis T3 with LEN()s going to B5...

Because the length of the inventory dataset may vary on subsequent openings the Inventory!r$A$1:$D$6548 stipulation needs to change dynamically.


I am calculating the ranges I need using:
lngInventoryRows = wbAnalysis.Worksheets("Inventory").Cells(65536, "A").End(xlUp).Row

lngBOMRows = wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row

Set rngInventory = wbAnalysis.Worksheets("inventory").Range("A1:D" & lngInventoryRows)

Set rngBOM = wbAnalysis.Worksheets("Analysis").Range("R3:R" & lngBOMRows)

How do I insert this information to the lookup to make it dynamic?

Thanks,
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
perhaps an Excel sample is good to help us imagine/visualize your problem?
Michael FowlerSolutions ConsultantCommented:
You can use VLOOKUP directly in VBA using the format

Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)
http://www.exceltrick.com/formulas_macros/vlookup-in-vba/

Since the input into the VLOOKUP command is a string this can be built as required using variables.
eg

Application.VLOOKUP(CStr(Range("B3").Value), rngInventory, 2, 0)

Open in new window


Note: to get the last row use
lngInventoryRows = wbAnalysis.Worksheets("Inventory").Range("A" & Rows.Count).End(xlUp).Row

Open in new window

as this will allow for all versions of Excel

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary CroxfordOperations Support AnalystAuthor Commented:
I have attached a workbook to illustrate the question.

The macro-enabled version of this workbook imports data to the Inventory sheet and the data in columns A-P on the Analysis sheet.

The next time the workbook is opened either or both of the imported datasets may be of different lengths than in this example.

In my code so far I find the range of the Inventory dataset and write that to rngInventory

VLOOKUP(TEXT(B3,"@"),Inventory!$A$1:$D$6548,2,0)

How do I write this VLOOKUP in vba replacing 'Inventory!$A$1:$D$6548' with rngInventory?
PreProjectAnalysis.xlsx
PreProjectAnalysis.xlsm
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in your formula, you can try:

replace Inventory!$A$1:$D$6548 to INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory"))

in which you need to add a macro function like this:

Function getLastRow(wsname As String) As String
    On Error GoTo Err
    Dim ws As Worksheet
    Set ws = Worksheets(wsname)
    getLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Exit Function
Err:
    getLastRow = ""
End Function

Open in new window


For example in Column S (Alloc), you can change the formula for row 3 from:

=IF(LEN(B3)>1,IF(ISNA(VLOOKUP(TEXT(B3,"@"),Inventory!$A$1:$D$6548,3,0)),"NULL",VLOOKUP(TEXT(B3,"@"),Inventory!$A$1:$D$6548,3,0)),
IF(LEN(D3)>1,IF(ISNA(VLOOKUP(TEXT(D3,"@"),Inventory!$A$1:$D$6548,3,0)),"NULL",VLOOKUP(TEXT(D3,"@"),Inventory!$A$1:$D$6548,3,0)),
IF(LEN(F3)>1,IF(ISNA(VLOOKUP(TEXT(F3,"@"),Inventory!$A$1:$D$6548,3,0)),"NULL",VLOOKUP(TEXT(F3,"@"),Inventory!$A$1:$D$6548,3,0)),
IF(LEN(H3)>1,IF(ISNA(VLOOKUP(TEXT(H3,"@"),Inventory!$A$1:$D$6548,3,0)),"NULL",VLOOKUP(TEXT(H3,"@"),Inventory!$A$1:$D$6548,3,0)),
IF(LEN(J3)>1,IF(ISNA(VLOOKUP(TEXT(J3,"@"),Inventory!$A$1:$D$6548,3,0)),"NULL",VLOOKUP(TEXT(J3,"@"),Inventory!$A$1:$D$6548,3,0)))))))

to:

=IF(LEN(B3)>1,IF(ISNA(VLOOKUP(TEXT(B3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),"NULL",VLOOKUP(TEXT(B3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),
IF(LEN(D3)>1,IF(ISNA(VLOOKUP(TEXT(D3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),"NULL",VLOOKUP(TEXT(D3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),
IF(LEN(F3)>1,IF(ISNA(VLOOKUP(TEXT(F3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),"NULL",VLOOKUP(TEXT(F3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),
IF(LEN(H3)>1,IF(ISNA(VLOOKUP(TEXT(H3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),"NULL",VLOOKUP(TEXT(H3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),
IF(LEN(J3)>1,IF(ISNA(VLOOKUP(TEXT(J3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)),"NULL",VLOOKUP(TEXT(J3,"@"),INDIRECT("Inventory!$A$1:$D$" & getLastRow("Inventory")),3,0)))))))
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I saw that in your workbook_open event, you actually trying to place the formula dynamically there...

strVLOOKUP_R = "=If(Len(B3)>1,IF(ISNA(VLOOKUP(TEXT(B3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0))," & strNull & ",VLOOKUP(TEXT(B3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0)),"
strVLOOKUP_R = strVLOOKUP_R & "ISNA(VLOOKUP(TEXT(D3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0))," & strNull & ",VLOOKUP(TEXT(D3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0)),"
strVLOOKUP_R = strVLOOKUP_R & "IF(LEN(F3)>1,IF(ISNA(VLOOKUP(TEXT(F3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0))," & strNull & ",VLOOKUP(TEXT(F3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0)),"
strVLOOKUP_R = strVLOOKUP_R & "IF(LEN(H3)>1,IF(ISNA(VLOOKUP(TEXT(H3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0))," & strNull & ",VLOOKUP(TEXT(H3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0)),"
strVLOOKUP_R = strVLOOKUP_R & "IF(LEN(J3)>1,IF(ISNA(VLOOKUP(TEXT(J3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0))," & strNull & ",VLOOKUP(TEXT(J3," & strAtSymbol & ")," & wbAnalysis.Worksheets("Analysis").Cells(65536, "P").End(xlUp).Row & ",2,0)))))))"

Open in new window


what you need to do is to assign this variable to cell's formula, like:

Cells(row, col).FormulaR1C1 = strVLOOKUP_R

will this approach makes sense to you?
Gary CroxfordOperations Support AnalystAuthor Commented:
Ryan , Michael,

I struggled with both of the solutions, kept throwing all kinds of errors

 I ended up using:

For Each cl In Table1  
11.  Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4], R3C8:R13C9, 2, False)"  
12.  Dept_Row = Dept_Row + 1  
13.  ctr = ctr + 1  
14.Next cl  

from the exceltips.com link

but I wanted to thank you for your efforts on my behalf
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.