I have workbook with approximately 50 Name Ranges that I have defined for my
Macros to Access. My macros used to work perfectly.

Yesterday when I activate the Macro, the macro gave me edebug messages. In
Excel, when I was verifiying the Name Ranges, about 20 of them defaulted to
#ref. I went an reset them up. They changed agian when I ran the macro.
Instead of referencing different cells, they all reference A1.

Is there a limitation to Name Ranges? Is there a way to keep the worksheet
referencing the correct cell?

Thanks
Dana

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

It sounds as though you are doing some that is messing up the ranges that
they reference, such as deleting a row. But it is hard to be precise without
details.

--
---
HTH

Bob

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



"Fredriksson via OfficeKB.com" <u27002@uwe wrote in message
news:6e7c7d7eb2af7@uwe...
I have workbook with approximately 50 Name Ranges that I have defined for
my
Macros to Access. My macros used to work perfectly.

Yesterday when I activate the Macro, the macro gave me edebug messages. In
Excel, when I was verifiying the Name Ranges, about 20 of them defaulted
to
#ref. I went an reset them up. They changed agian when I ran the macro.
Instead of referencing different cells, they all reference A1.

Is there a limitation to Name Ranges? Is there a way to keep the
worksheet
referencing the correct cell?

Thanks
Dana

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

Thanks. I checked my ranges and one of them was deleting the others when
called by the macro.

Thanks

Bob Phillips wrote:
It sounds as though you are doing some that is messing up the ranges that
they reference, such as deleting a row. But it is hard to be precise without
details.

I have workbook with approximately 50 Name Ranges that I have defined for
my
[quoted text clipped - 12 lines]
Thanks
Dana

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

Did you use absolute references in your refers to definitions

=Sheet1!$B$9:$B$20

rather than

=sheet1!B9:B20

I have never had a problem with references that were absolute unless I
deleted the range they referred to.

If you want the range to be constant regardless of deletions:

=Indirect("Sheet1!B9:B20")

--
Regards,
Tom Ogilvy


"Fredriksson via OfficeKB.com" wrote:

I have workbook with approximately 50 Name Ranges that I have defined for my
Macros to Access. My macros used to work perfectly.

Yesterday when I activate the Macro, the macro gave me edebug messages. In
Excel, when I was verifiying the Name Ranges, about 20 of them defaulted to
#ref. I went an reset them up. They changed agian when I ran the macro.
Instead of referencing different cells, they all reference A1.

Is there a limitation to Name Ranges? Is there a way to keep the worksheet
referencing the correct cell?

Thanks
Dana

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

Thanks I took your suggestion and change all to absolute references
Tom Ogilvy wrote:
Did you use absolute references in your refers to definitions

=Sheet1!$B$9:$B$20

rather than

=sheet1!B9:B20

I have never had a problem with references that were absolute unless I
deleted the range they referred to.

If you want the range to be constant regardless of deletions:

=Indirect("Sheet1!B9:B20")

I have workbook with approximately 50 Name Ranges that I have defined for my
Macros to Access. My macros used to work perfectly.
[quoted text clipped - 9 lines]
Thanks
Dana

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1