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?
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?