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

[msquared99]

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]

[vba]
'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...)

[/vba]

It might be easier to build the formula String slowly
[VBA]
Dim FString As String
FString = "=VLOOKUP(D4,CHOOSE({2,1},'"
FString = FString & WBName & "'Sheet1" & "$D$3:$D$D"
FString = FString & CStr(LastRow)
FString = '...
[/VBA]

[msquared99]

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]

I'm wondering if INDEX and MATCH would not be a better option.

I'm losing many hairs over this!

[Paul_Hossler]

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

[VBA]
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)"
[/VBA]

Paul

[SamT]

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.
[vba]'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
[/vba]

[msquared99]

Thanks Paul and Sam.

I will work with those ideas.

Again thanks for your time.

Mike

[Aflatoon]

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.

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

[msquared99]

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]

If that's what you get, then WBName must include the full path, in which case you can use something like:
[vba]
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))"
[/vba]

[msquared99]

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]

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]

You could also use
[vba]dim wb as workbook
Set wb = Workbooks.Open(FileName)[/vba]
And then just refer to wb.name