| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup in VBA
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") |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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") |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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") |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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") |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
You are welcome! Thanks for the feedback!
Stefi Mike H. ezt *rta: 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") |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLookup in VBA
Agreed! In fact I learned an optional referring method from you. Earlier I
always used Workbooks("PRProc.xls").Names("PRLUrng").RefersToR ange style references, from now on I can consider using Workbooks("PRProc.xls").Sheets("Accounts").Range(" prlurng") method. Regards, Stefi Dave Peterson ezt *rta: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Linear Mode
