PDA

View Full Version : Solved: VLOOKUP Syntax in VBA Using WB w-Dynamic Name



msquared99
03-22-2013, 10:09 AM
I have a macro that allows the user to choose a workbook from a folder and assigns the workbook a name and the name is WBName. The reason is because this workbooks name always changes. Here is the part of the code that does opens the selected workbook:

WBName = fd.SelectedItems(1)
Workbooks.Open (WBName)

WBName is open but the active workbook is Sales Report.xls, this happens on down in the code a bit.

I want to do a VLOOKUP to bring data into the Sales Report from WBName via my macro. If typed into a cell here is what it would look like:

=VLOOKUP(D4,CHOOSE({2,1},'[Sales 03-20-13.xls]Sheet1'!$D$3:$D$200,[Sales 03-20-13.xls]Sheet1'!$G$3:$G$200),2,0)

In my VBA code Sales 03-20-13.xls is given the name WBName.


How would I write the VLOOKUP in my VBA code?

I know it is not going to be:

Range("J3").Formula = "=VLOOKUP(D4,CHOOSE({2,1},WBName.Sheet1.Range("$D$3:$D$" & LastRow),WBName.Sheet1.Range("$G$3:$G$" & LastRow),2,0)"

I've tried several variations and have failed.

Thanks!

SamT
03-22-2013, 04:04 PM
'if fd.SelectedItems returns a string. You have to get WBName to a string
Dim WBName As String
WBName = fd.SelectedItems(1)
Workbooks.Open (WBName)

'Assumes LastRow is a Long. Modify as needed
'Note that "'" is quoted single quote and "' is quote + single quote
'Single quotes surround Workbook names in formulas.

(First Part Of Formula, up to ,WB...) & "'" & WBName & "'Sheet1" & "$D$3:$D$D" & CStr(LastRow) & "," & (Rest of formula...)



It might be easier to build the formula String slowly

Dim FString As String
FString = "=VLOOKUP(D4,CHOOSE({2,1},'"
FString = FString & WBName & "'Sheet1" & "$D$3:$D$D"
FString = FString & CStr(LastRow)
FString = '...

msquared99
03-22-2013, 05:35 PM
Hey Sam!

Thanks for the help.

fd.SelectedItems is a String
WBName is a String
LastRow is Long

Here is the formula:
Range("E3").Formula = "=VLOOKUP(D4,CHOOSE({2,1}, & " '" & WBName & "'Sheet1" & "$D$3:$D$D" & CStr(lastRow1) & "," & "'" & WBName & "'Sheet1" & "$G$3:$G$G" & CStr(LastRow1),2,0)"

I believe I'm not doing something correct with the " and '

Once I'm done entering the formula it adds a ' like you would a comment and turns green just like adding a comment.

Pulling my hair out!

Thanks.

msquared99
03-22-2013, 09:16 PM
I'm wondering if INDEX and MATCH would not be a better option.

I'm losing many hairs over this!

Paul_Hossler
03-23-2013, 07:08 PM
I think that you need a single quote around the WB name since there's spaces in it

I can never translate a WS formula to VBA by building it all in one go, so I usually just build it a piece at a time

For ex, I'd even build the Sheet references piecemeal just to be able to debug them

Anyway, try something like this with the single quotes


Dim sFormula As String, sWBname As String

sWBname = "Sales 03-20-13.xls"
sWBname = "['" & sWBname & "']" '-- Since there's a space in the WB name
'-- I THINK you need the single quotes around it

sFormula = "=VLOOKUP(D4,CHOOSE({2,1},"
sFormula = sFormula & "Sheet1'!$D$3:$D$200,"
sFormula = sFormula & "Sheet1'!$G$3:$GD$200,2,0)"


Paul

SamT
03-23-2013, 10:42 PM
Set a Brweak Point at "WBName = fd.SelectedItems(1)." Place the cursor over the X at the first "X=". SteP thru the code with F8, and watch the value of X.

Alternately, you set a Watch on X and view its value in the Immediate Window.
'Handy TS technique when building strings
Dim X
WBName = fd.SelectedItems(1)
X = WBName
'Stop the macro. Write the formula long hand with the Fd.Item instead of WBName
'Compare it to X as the code progresses

WBName = "'" & WBName & "'" 'Surround WBName with 's just once
X = WBName 'Look for single quotes here

Dim FString As String

Fstring = "=VLOOKUP(D4,CHOOSE({2,1},"
X =FString
FString = FString & WBName
X =FString
FString = FString & "Sheet1!$D$3:$D" 'Too many $D's
X =FString
FString = FString & CStr(lastRow1) & ","
X =FString
FString = FString & WBName
X =FString
FString = FString & "Sheet1!$G$3:$G" 'Too many $G's
X =FString & CStr(LastRow1)
X =FString
FString = FString & ",2, 0)"
X =FString
'When code works, remove all X='s and Dim X

msquared99
03-24-2013, 10:27 AM
Thanks Paul and Sam.

I will work with those ideas.

Again thanks for your time.

Mike

Aflatoon
03-25-2013, 03:36 AM
I'm wondering if INDEX and MATCH would not be a better option.


Most definitely - I can see no point in using VLOOKUP for this. ;)

Range("E3").Formula = "=INDEX('[" & WBName & "]Sheet1'!$D$3:$D$200,MATCH(D4,'[" & WBname & "]Sheet1'!$G$3:$G$200,0))"

msquared99
03-25-2013, 09:28 AM
I'm giving up on the VLOOKUP.

However, INDEX & MATCH, the code Aflatoon gave works but instead of referring to the open workbook it refers to the saved workbook in the folder. Remember WBName = Any AR 03.01.13 PM.xls.

The formula VBA writes should look like this referring to the open workbook:
=INDEX('[Any AR 03.01.13 PM.xls]Sheet1'!($D$3:$D170,MATCH(D4,'[Any AR 03.01.13 PM.xls]Sheet1'!($H$3:$H170,0))

But it looks like this which is referring to the workbook in the folder and shows #N/A and should show TESTING:
=INDEX('[C:\Temp\[Any AR 03.01.13 PM.xls]Sheet1]Any AR 03.01.13 PM.xls]Sheet1'!$D$3:$D170,MATCH(D4,'[C:\Temp\[Any AR 03.01.13 PM.xls]Sheet1]Any AR 03.01.13 PM.xls]Sheet1'!$H$3:$H170,0))

When I play around with the code I get either a Complie Error Invalid Character or a Run-time Error 1004.

Aflatoon
03-25-2013, 09:40 AM
If that's what you get, then WBName must include the full path, in which case you can use something like:

Dim FileName as String
FileName = Split(WBName, "\")(UBound(Split(WBName, "\"))
Range("E3").Formula = "=INDEX('[" & FileName & "]Sheet1'!$D$3:$D$200,MATCH(D4,'[" & FileName & "]Sheet1'!$G$3:$G$200,0))"

msquared99
03-25-2013, 10:52 AM
Let me ask this, here is how I get FileName:

[Sub ChooseFile()
Dim fd As FileDialog
Dim FileName As String
Dim lastRow1 As Long
Dim lastRow2 As Long
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Get the number the button chosen.
Dim FileChosen As Integer
FileChosen = fd.Show
If FileChosen <> -1 Then
'Didn't choose anything (clicked cancel).
MsgBox "No file opened."


Else
'Display name and path of file chosen.
FileName = fd.SelectedItems(1)
Workbooks.Open (FileName)]

Would after I get the file open in the above then do DIM FileName As String?

msquared99
03-25-2013, 11:24 AM
I got it to work!

I needed this line in my code:FileName = Mid(FileName, InStrRev(FileName, "\") + 1, Len(FileName))

Works like a charm!

Thanks for everyones help. I really learned a lot from this one!

Aflatoon
03-25-2013, 12:59 PM
You could also use
dim wb as workbook
Set wb = Workbooks.Open(FileName)
And then just refer to wb.name