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

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

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

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

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