VBA adding variable (filename) to a vlookup

toneloc

New Member
Joined
Sep 22, 2009
Messages
7
Hi,

I am trying to pass a string variable to a vlookup formula in VBA but the output is not working correctly.

I have the following code :-

Dim cFileName As String

MsgBox "Please browse for the latest CAT file on your hard drive, and select"
cFileName = Application.GetOpenFilename
'They have cancelled.
If cFileName = "False" Then Exit Sub
Workbooks.Open Filename:=cFileName

Windows("Voucher Backupxxxxxxx.xls").Activate

Sheets("LOH").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[" & cFileName & "]HC'!E:G,3,FALSE)"

The output from this is as follows :-

=VLOOKUP(B7,'[C:\Documents and Settings\.........\Test\CAT.xls]HC'!E:G,3,FALSE)

Which results in #NAME as the "[" bracket does not bracket the CAT.xls] but appears at the beginning of the filename but I can't figure out how to fix it.

Can anyone assist please?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RolfJ

Banned
Joined
Jul 29, 2009
Messages
333
Give this a try:

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3]," & "[" & cFileName & "]" & "HC!E:G,3,FALSE)"

Hope this helped,
Rolf
 

toneloc

New Member
Joined
Sep 22, 2009
Messages
7
Thanks very much for the quick response - I have tried this but am getting an application-defined or object-defined error.

I think it is now correctly bracketing the filename [CAT.xls] but think it is also putting [ ] around the entire pathname
 

toneloc

New Member
Joined
Sep 22, 2009
Messages
7

ADVERTISEMENT

Have sorted this - had to remove the full path from the filename


Dim posn As Integer, i As Integer
Dim fName As String

'Create short file name for CAT file
posn = 0
'find the position of the last "\" character in filename
For i = 1 To Len(cFileName)
If (Mid(cFileName, i, 1) = "\") Then posn = i
Next i
'get filename without path
fName = Right(cFileName, Len(cFileName) - posn)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,978
Messages
5,767,419
Members
425,411
Latest member
sarmientojayexce03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top