I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("A1:F 65000")

Why not just use whole columns

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("A:F" )



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mike H." wrote in message
...
I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng =
Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")

I don't have a problem using the range, but I still get the run-time error 9,
subscript out of range when I use the line below:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("A:F" )

I am NOT in that workbook when I execute the code, if that makes a difference?

Set lookuprng = Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange

Regards,
Stefi

€žMike H.€� ezt Ã*rta:

I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")

Referring to the named range this way works perfectly. Thanks!

"Stefi" wrote:

Set lookuprng = Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange

Regards,
Stefi

€žMike H.€� ezt Ã*rta:

I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")

I don't think it was going through the names collection that solved the
problem. I think it was that Stefi added the .xls in the filename:

Set lookuprng = Workbooks("PRProc.xls").Sheets("Accounts").Range(" a:f")
or
Set lookuprng = Workbooks("PRProc.xls").Sheets("Accounts").Range(" prlurng)



Mike H. wrote:

Referring to the named range this way works perfectly. Thanks!

"Stefi" wrote:

Set lookuprng = Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange

Regards,
Stefi

€žMike H.€� ezt Ã*rta:

I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")



--

Dave Peterson

Partly yes, but there is another difference:

Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange
doesn't require specifying sheet,

Workbooks("PRProc.xls").Sheets("Accounts").Range(" prlurng")
does.

Regards,
Stefi

€žDave Peterson€� ezt Ã*rta:

I don't think it was going through the names collection that solved the
problem. I think it was that Stefi added the .xls in the filename:

Set lookuprng = Workbooks("PRProc.xls").Sheets("Accounts").Range(" a:f")
or
Set lookuprng = Workbooks("PRProc.xls").Sheets("Accounts").Range(" prlurng)



Mike H. wrote:

Referring to the named range this way works perfectly. Thanks!

"Stefi" wrote:

Set lookuprng = Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange

Regards,
Stefi

€žMike H.€Â� ezt ÃÂ*rta:

I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")



--

Dave Peterson

But I don't think that that is what made your code work. I bet it would have
had the same problem if you had used:

Workbooks("PRProc").Names("PRLUrng").RefersToRange



Stefi wrote:

Partly yes, but there is another difference:

Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange
doesn't require specifying sheet,

Workbooks("PRProc.xls").Sheets("Accounts").Range(" prlurng")
does.

Regards,
Stefi

€žDave Peterson€� ezt Ã*rta:

I don't think it was going through the names collection that solved the
problem. I think it was that Stefi added the .xls in the filename:

Set lookuprng = Workbooks("PRProc.xls").Sheets("Accounts").Range(" a:f")
or
Set lookuprng = Workbooks("PRProc.xls").Sheets("Accounts").Range(" prlurng)



Mike H. wrote:

Referring to the named range this way works perfectly. Thanks!

"Stefi" wrote:

Set lookuprng = Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange

Regards,
Stefi

€žMike H.€Â� ezt ÃÂ*rta:

I am trying to use the Vlookup function in VBA and the lookup is to an
external workbook. I get a runtime Error 9 when setting the lookup range:

Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("$a$1 :$f$65000")

Actually, my reference is a named range so I'd rather refer to the name
instead, but not sure the syntax for that either:
Set lookuprng = Workbooks("PRProc").Sheets("Accounts").Range("PRLU Rng")



--

Dave Peterson


--

Dave Peterson