Variable Vlookup vba

[greyangel]

Good afternoon everyone,


I am trying to create a vlookup formula with a variable range. The example of my code is below



Cells.Find(Trim("POOL %"), LookIn:=xlValues).Select

Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 18)).Select
Range(Selection, Selection.End(xlDown)).Select

myRanges = Selection.Address

indexlocation = Range("C4").Value

Range("B14").Formula = "=+Pullafterlast(B3,""\"")"
Range("B15").Formula = "=+LEFT(B3,FIND( B14,B3)-2)"
fluidfilepath = Range("B14").Value
fluidvariable = Range("B15").Value

Range("D8").FormulaR1C1 = _
"=VLOOKUP(RC[-3],'" & fluidvariable & "\" & "[" & fluidfilepath & "]""INVbyLIFOpool'!" & myRanges & ",10,FALSE)"


Whenever I get to the vlookup I get an error message. I have tried my vlookup formula without the variable myranges and it seems to work fine. Does anybody have any recommendations?

[SamT]

& "]'INVbyLIFOpool'!" &

When I must use VBA to build a Formula String that contains Quotes, I use

Const SQ as String = "'"
Const DQ as String = """

MyFormula = "This and " & SQ & "That" & SQ & " the " & DQ & "Other." & DQ

Resulting String = This and 'That' and the "Other."

[greyangel]

& "]'INVbyLIFOpool'!" &

When I must use VBA to build a Formula String that contains Quotes, I use

Const SQ as String = "'"
Const DQ as String = """

MyFormula = "This and " & SQ & "That" & SQ & " the " & DQ & "Other." & DQ

Resulting String = This and 'That' and the "Other."


I got a "Run-time error '1004: Application-defined or object-defined error"

[SamT]

What did you do?

[greyangel]

What did you do?

I think it has to do with how I defined myranges

Below is what my code does. In cell A1 I have the full file path to a workbook. I then assign certain segments of this path to variables so that I may use it in the Vlookup formula.


Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 18)).Select
Range(Selection, Selection.End(xlDown)).Select
myRanges = Selection.Address

msgbox myRanges
'(This pulls the range variable that I want)

inventoryfilepath = "inventory by Area of Resp May 2017.xlsx

'(Achieved by using a vba created function that pulls everything to the right of the \)(This is located in cell A3)



inventoryvariable = "C:\Users\Jdoe\documents\working on macro

'(Achieved by using Left(A1,find(A3,A1)-2)

'Finally this is the part in which it bombs out.

Range("B8").FormulaR1C1 = _
"=VLOOKUP(RC[-2],'" & inventoryvariable & "\" & "[" & Inventoryfilepath & "]'INV by LIFOpool'!" & myRanges & ",4,FALSE)"


I think it has to do with how I define myranges.

[SamT]

In column B, RC[-2] refers to the (non existent) column to the left of column A.

I gotta say that your variable names leave a bit to be desired.
May I suggest
InventoryFilePath
InventoryWkBk
InventorySht
and InventoryTable

Reading inventoryvariable & Inventoryfilepath & 'INV by LIFOpool'!" & myRanges took some thought and back reading to troubleshoot. Especially inventoryvariable & Inventoryfilepath. the path is supposed to be first in line

InventoryFilePath & InventoryWkBk & InventorySht & InventoryTable, would have been self explanatory

Yeah, I left out all the "Separator marks" in the string

[greyangel]

In column B, RC[-2] refers to the (non existent) column to the left of column A.

I gotta say that your variable names leave a bit to be desired.
May I suggest
InventoryFilePath
InventoryWkBk
InventorySht
and InventoryTable

Reading inventoryvariable & Inventoryfilepath & 'INV by LIFOpool'!" & myRanges took some thought and back reading to troubleshoot. Especially inventoryvariable & Inventoryfilepath. the path is supposed to be first in line

InventoryFilePath & InventoryWkBk & InventorySht & InventoryTable, would have been self explanatory

Yeah, I left out all the "Separator marks" in the string

Thank you I will remember this in the future I am not going to change it for this code because I have to many items in my code that refers to that document. I think I found one of my errors "myranges" would show a value like $A$1:$P$55, however my formula code is setup for R1C1 so it doesn't like the "myranges" value. I decided to drop the R1C1 and got the following code.


Range("B8").Formula = _
"=VLOOKUP(A8,'" & inventoryvariable & "\" & "[" & inventoryfilepath & "]INVbyLIFOpool'!" & myranges & ",7,FALSE)"

However, this code brings up an error as well.

[greyangel]

Thank you I will remember this in the future I am not going to change it for this code because I have to many items in my code that refers to that document. I think I found one of my errors "myranges" would show a value like $A$1:$P$55, however my formula code is setup for R1C1 so it doesn't like the "myranges" value. I decided to drop the R1C1 and got the following code.


Range("B8").Formula = _
"=VLOOKUP(A8,'" & inventoryvariable & "\" & "[" & inventoryfilepath & "]INVbyLIFOpool'!" & myranges & ",7,FALSE)"

However, this code brings up an error as well.

Nevermind... re-ran it and it works now. Thank you for all the help.

[SamT]

I dislike R1C1 style. Imagine figgering out R53C-127 Compared to $D$2