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,