Excel - Conflict with Valid Range Reference Error in Excel 2007

Asked By Vams on 02-Mar-09 08:01 AM
Hi,

While converting the Macros enabled Microsoft Excel 2003 to Microsoft Excel
2007 format, I am getting the following Conflict with Valid Range Reference
Error. After clicking on the Ok\OK to All buttons the VBA code in the Excel
is not working as it is expected to.

The following is the Error message.

invalid for excel. The name has been replaced with _rev02.

You may still need to manually update any references to this name used in
VBA code or as text arguments in functions. You must close and reopen the
workbook before these changes take effect."

and prompting for Ok\OK to All button.

Please pass on any pointers for resolving this issue.

--
Thanks in Advance,
Vamsi




JLGWhi replied on 02-Mar-09 08:44 AM
It is telling you that in xl2007, it has a column header REV which causes a
conflict with with a file name or some other name in your xl2003 files that
contain rev, so you need to use a subscript line (_) to distinguish that what
you are transferring in is not a range reference.  Excel has done it for you,
but you need to save the file to make the change effective.
Dave Peterson replied on 02-Mar-09 08:46 AM
xl2007 has 16k columns.

That means that names that worked ok in xl2003 and below may look like addresses
of cells in xl2007 (IV isn't the last column anymore).

You'll have to change the name.

And excel won't change your code--you'll have to do that yourself.  That's what
the warning means.

And excel won't change any formulas that use that name as text:
=indirect("rev02")
won't get fixed automatically.


--

Dave Peterson
Vams replied on 03-Mar-09 06:17 AM
Hi Dave,

Can you please elaborate on your reply.
Dave Peterson replied on 03-Mar-09 08:29 AM
There are some strings that can't be used for names--things that look like
addresses of ranges.  There are characters that are invalid in names, so they
can't be used.

Your name looks like a cell address.  Excel won't allow it to be used.


--

Dave Peterson