Name Ranges Acting Up

  • Thread starter Thread starter Fredriksson via OfficeKB.com
  • Start date Start date
F

Fredriksson via OfficeKB.com

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
 
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)
 
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")
 
Thanks. I checked my ranges and one of them was deleting the others when
called by the macro.

Thanks

Bob said:
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
Click to expand...
[quoted text clipped - 12 lines]
Thanks
Dana
Click to expand...
Click to expand...
 
Thanks I took your suggestion and change all to absolute references
Tom said:
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.
Click to expand...
[quoted text clipped - 9 lines]
Thanks
Dana
Click to expand...
Click to expand...
 
Back
Top