VLookup help !!

[Kelvin]

Can anyone help with this.
I have give a workbook a variable name . When I try to call it in the
Vlookup formula it does not recognise the file name.
Here is the sequence

dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " +
dltr + ".xls")
mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],swb.worksheets(1)!C1:C35," & hdr
& ",0)"

Can anyone tell me how to use swb within my Vlookup?

Thanks
Kelvin

 

[Dave Peterson]

maybe...

dim myLookupRng as range
dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " _
& dltr & ".xls")

with swb.worksheets(1)
set mylookuprng = .range("a:ai")
end with

mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-14]," & mylookuprng.address(external:=true) _
& "," & hdr & ",0)"

Can anyone help with this.
I have give a workbook a variable name . When I try to call it in the
Vlookup formula it does not recognise the file name.
Here is the sequence

dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " +
dltr + ".xls")
mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],swb.worksheets(1)!C1:C35," & hdr
& ",0)"

Can anyone tell me how to use swb within my Vlookup?

Thanks
Kelvin

 

[Kelvin]

Hello Dave,
Thanks for the input. I am recieving and error with the Vlookup?
Will keep trying.
--
KWB

maybe...

dim myLookupRng as range
dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " _
& dltr & ".xls")

with swb.worksheets(1)
set mylookuprng = .range("a:ai")
end with

mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-14]," & mylookuprng.address(external:=true) _
& "," & hdr & ",0)"

Can anyone help with this.
I have give a workbook a variable name . When I try to call it in the
Vlookup formula it does not recognise the file name.
Here is the sequence

dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " +
dltr + ".xls")
mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],swb.worksheets(1)!C1:C35," & hdr
& ",0)"

Can anyone tell me how to use swb within my Vlookup?

Thanks
Kelvin

 

[Dave Peterson]

Maybe there's no match???

What's in hdr??

Maybe you're not in columns 1 to 35???

Hello Dave,
Thanks for the input. I am recieving and error with the Vlookup?
Will keep trying.
--
KWB

maybe...

dim myLookupRng as range
dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " _
& dltr & ".xls")

with swb.worksheets(1)
set mylookuprng = .range("a:ai")
end with

mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-14]," & mylookuprng.address(external:=true) _
& "," & hdr & ",0)"

Can anyone help with this.
I have give a workbook a variable name . When I try to call it in the
Vlookup formula it does not recognise the file name.
Here is the sequence

dltr = InputBox(" Enter date of file (Ex: 12_07 ):")
Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " +
dltr + ".xls")
mwb.Activate
Range("o1") = "column title"
Range("o2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],swb.worksheets(1)!C1:C35," & hdr
& ",0)"

Can anyone tell me how to use swb within my Vlookup?

Thanks
Kelvin