View Full Version : [SOLVED:] Variable Vlookup vba
greyangel
07-24-2017, 11:05 AM
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?
& "]'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
07-24-2017, 01:59 PM
& "]'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"
greyangel
07-25-2017, 05:20 AM
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.
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
07-25-2017, 09:36 AM
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
07-25-2017, 10:01 AM
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.
I dislike R1C1 style. Imagine figgering out R53C-127 Compared to $D$2
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.