Changing Relative to Absolute and visa/versa

I have this macro which was donated to me to help with my editing/updating of many formulas from Relative to Absolute, but the macro returns #VALUE! on all of my larger formulas. It seems to handle smaller formulas well, but the ones I really need it for it returns #VALUE! and clears the contents of the cell.

I'm hoping someone here can look this over for me and see if they can identify any reason it is behaving like this and if there are any changes I can make to have it work on my larger formulas.

I'm using Excel 2000

Thanks.

Please Login or Register  to view this content.

if you can insert add ins try asap utilities in formulas there is a convert referencing option of all 4 options
http://www.asap-utilities.com/

Thanks VA and Martin,

I need to avoid add-ins if possible.

I checked out the link to ozgrid and added the "Slow" code but I am still having problems. I have no clue as to why.

Is it me?

The macro below works on this formula:

Please Login or Register  to view this content.

But not on this:

Please Login or Register  to view this content.

Any Ideas?

Please Login or Register  to view this content.

I changed all the "Range Names" to cell references and it still did the same thing, so I Stepped Thru the macro and then did some counting and the formula is greater than 255 characters so it failed the

Please Login or Register  to view this content.

So I edited out the If < 255 part and got the #VALUE! so now I know where that previous problem comes from.

So now I guess I am looking for a way to dissasemble rCell.Formula into 254 character blocks, run the macro on each block, then reassemble rCell.Formula into the original formula and put it back whole. If that's even a possibility, if VBA will process a parsed formula like that.

Any suggestions on how I can parse the 254 character blocks in VBA ? or if it's even an option that it will work parsing the formula like that?

Thanks.