Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errors when using range names in VBA
Hi,
I have a dynamic range name set as MyProd, I used =OFFSET((sheet1!$B$5,0,0,count(sheet1!$B:$B),5) in the Names menu to define this. However, since setting this up, the Macro I had that looked up data in a column from the range MyProd, returns errors as if the range address isn't known. Is there as solution to resolve this eg. somehow define the address before running the macro? Hoping someone can give some direction. Thanks, Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errors when using range names in VBA
Are you sure that it is not that column B has no numbers?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rob" wrote in message ... Hi, I have a dynamic range name set as MyProd, I used =OFFSET((sheet1!$B$5,0,0,count(sheet1!$B:$B),5) in the Names menu to define this. However, since setting this up, the Macro I had that looked up data in a column from the range MyProd, returns errors as if the range address isn't known. Is there as solution to resolve this eg. somehow define the address before running the macro? Hoping someone can give some direction. Thanks, Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errors when using range names in VBA
Bob,
Have checked data and its all there when looking at the range name. It also works when I simply just name the range. Regards, Rob "Bob Phillips" wrote in message ... Are you sure that it is not that column B has no numbers? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rob" wrote in message ... Hi, I have a dynamic range name set as MyProd, I used =OFFSET((sheet1!$B$5,0,0,count(sheet1!$B:$B),5) in the Names menu to define this. However, since setting this up, the Macro I had that looked up data in a column from the range MyProd, returns errors as if the range address isn't known. Is there as solution to resolve this eg. somehow define the address before running the macro? Hoping someone can give some direction. Thanks, Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errors when using range names in VBA
if i enter data into enter data into b5:b25
then i fix your formula: =OFFSET(sheet1!$B$5,0,0,count(sheet1!$B:$B),5) and then type this in the immedidate window: ?range("myprod").Address it returns: $B$5:$F$25 -- Gary "Rob" wrote in message ... Hi, I have a dynamic range name set as MyProd, I used =OFFSET((sheet1!$B$5,0,0,count(sheet1!$B:$B),5) in the Names menu to define this. However, since setting this up, the Macro I had that looked up data in a column from the range MyProd, returns errors as if the range address isn't known. Is there as solution to resolve this eg. somehow define the address before running the macro? Hoping someone can give some direction. Thanks, Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errors when using range names in VBA
Thanks everyone, seem to have cracked it with $ sign, had $B5 and not $B$5.
Thanks, Rob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if i enter data into enter data into b5:b25 then i fix your formula: =OFFSET(sheet1!$B$5,0,0,count(sheet1!$B:$B),5) and then type this in the immedidate window: ?range("myprod").Address it returns: $B$5:$F$25 -- Gary "Rob" wrote in message ... Hi, I have a dynamic range name set as MyProd, I used =OFFSET((sheet1!$B$5,0,0,count(sheet1!$B:$B),5) in the Names menu to define this. However, since setting this up, the Macro I had that looked up data in a column from the range MyProd, returns errors as if the range address isn't known. Is there as solution to resolve this eg. somehow define the address before running the macro? Hoping someone can give some direction. Thanks, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
SumProduct When Range Has Errors | Excel Worksheet Functions | |||
Deleting names with errors | Excel Programming |