VLookup help !!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelvin - 05 Feb 2008 23:39 GMT

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

Signature

KWB

Reply to this Message

Dave Peterson - 06 Feb 2008 00:33 GMT

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
[quoted text clipped - 16 lines]
> --
> KWB

Signature

Dave Peterson

Reply to this Message

Kelvin - 06 Feb 2008 01:13 GMT

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

Signature

KWB

> maybe...
>
[quoted text clipped - 34 lines]
> > --
> > KWB

Reply to this Message

Dave Peterson - 06 Feb 2008 02:01 GMT

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?
[quoted text clipped - 44 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message