Solved How to change a lot of cells to absolute (macro fails?)

Microsoft corporation Office 2010 profes...
December 23, 2014 at 04:41:18
Specs: Windows 7
Hello all

Ive been using this macro a lot of times, but I am running into some strange problems with it.

Sub Absolute()
 Dim Cell As Range
 For Each Cell In Selection
 If Cell.HasFormula Then
 Cell.Formula = Application.ConvertFormula(Cell.Formula, _
 xlA1, xlA1, xlAbsolute)
 End If
 Next
 End Sub

If the code looks like this it doesnt work:

=IF(D2>0;IF(BI2=1;
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
Sheet2!$C$17;
"@@text1@@";A2);
"@@text2@@";B2);
"@@text3@@";C2);
"@@text4@@";D2);
"@@text5@@";E2);
"@@text6@@";F2);
"No match");"error")

But is it looks like this:

=IF(D2>0;IF(BI2=1;
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
Sheet2!$C$17;
"@@text1@@";A2);
"@@text2@@";B2);
"@@text3@@";C2);
"@@text4@@";D2);
"@@text5@@";F2);
"No match");"error")


It does Work and change all the references to absolutes. Ive been toying with this and its allway when there is 6 or more of those @@text@@ lines. How can that be?

I have like 2000 cells with code like that in them, so I really need to find an easy way to make them all absolute or I am going to be doing this manually for the next few weeks.


See More: How to change a lot of cells to absolute (macro fails?)

Report •


✔ Best Answer
January 2, 2015 at 23:52:00
Okay I think I must give up on this one... I have found a bit of a work around, where I find and replace A, B, C, D and so on in with the case sensitive setting turned on. I tho have to first find AA, AB, AC and replace them, and then later replace the single character column references like A, B, C. This limits the number of times I have to use the find and replace function to the number of functions I have in the formula, which is 19 at the moment. Altho it is a pretty ugly solution, its better than going though 2000 cells or more...


#1
December 23, 2014 at 20:21:34
I believe that there is a limitation as to how many nested SUBSTITUTE functions you can use.

Try some of the suggested techniques offered here...

http://www.mrexcel.com/forum/excel-...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 23, 2014 at 22:50:37
As I can read elsewhere those limitations changed with Excel 2007 from 7 to 64 functions nested within a single formula. And if I run my code (excel 2010) it also works fine and substitutes fine all nine old_text with nine new_texts within the same text string.

All I need is to find a way to make it absolutes.. Can you see anything wrong with the above macro that would suggest that it somehow fails? It returns #VALUE as an error.


Report •

#3
December 24, 2014 at 16:16:12
I don't understand what your exact issue is. What is it that you are asking about when you ask "How can that be?"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 24, 2014 at 23:25:50
The macro only works with 5 nested functions, not 6 and above.

The issue here is, that I need some sort of way to change all 2000 cells to absolute reference. The formula looks like those posted in #1. If I do a find and replace I am able to put a $ before the Column reference, but how do I put a $ before the row numbers in those references?


Report •

#5
December 26, 2014 at 08:50:54
re: All I need is to find a way to make it absolutes. Can you see anything wrong with the above macro that would suggest that it somehow fails? It returns #VALUE as an error.

Although I can't see your spreadsheet from where I am sitting, I can say this:

The macro itself is not returning a #VALUE error...only a function can return a #VALUE error. In other words, something in the code is changing the formula in the cell such that the formula, not the macro, now returns a #VALUE error.

A description of the #VALUE error can be found here:

http://www.excelfunctions.net/Excel...

I suggest that you use the Evaluate Formula feature and find out which portion of the resulting formula is returning the #VALUE error.

That said...

Unless I am missing something, I did not have any problem with the code when run against your "6 nested SUBSTITUTE" formula.

Since I can't use semi-colons, I first changed your formula to use commas:

=IF(D2>0,IF(BI2=1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
Sheet2!$C$17,"@@text1@@",A2),"@@text2@@",B2),
"@@text3@@",C2),"@@text4@@",D2),"@@text5@@",E2),
"@@text6@@",F2),"No match"),"error")

I then ran your macro against that formula and got this:

=IF($D$2>0,IF($BI$2=1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
Sheet2!$C$17,"@@text1@@",$A$2),"@@text2@@",$B$2),
"@@text3@@",$C$2),"@@text4@@",$D$2),"@@text5@@",$E$2),
"@@text6@@",$F$2),"No match"),"error")

If that is not what you are looking for, please be specific in describing what the issue is.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 26, 2014 at 10:38:47
Okay this is strange. Why does it return an #VALUE when I do the exact same thing?? I tried copying the exact code you have and changed the , back to ; and it returned the #VALUE error again. I can't go though the formula with the evaluation tool because the VALUE error somehow overwrites the whole formula.

Report •

#7
December 26, 2014 at 11:09:15
When you click in the cell that displays the #VALUE error, what do you see in the formula bar?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
December 26, 2014 at 12:41:05
I only see #VALUE, nothing else.

Report •

#9
December 26, 2014 at 14:08:17
I have never heard of that and since I can't replicate it, I can't offer any solution.

I found a site where someone seems to have a similar problem, although his formula bar shows =#VALUE while you don't seem to have the = sign.

In any case, no one had an answer for him, in fact most responders didn't even seem to understand the question.

http://superuser.com/questions/4113...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 2, 2015 at 23:52:00
✔ Best Answer
Okay I think I must give up on this one... I have found a bit of a work around, where I find and replace A, B, C, D and so on in with the case sensitive setting turned on. I tho have to first find AA, AB, AC and replace them, and then later replace the single character column references like A, B, C. This limits the number of times I have to use the find and replace function to the number of functions I have in the formula, which is 19 at the moment. Altho it is a pretty ugly solution, its better than going though 2000 cells or more...

Report •

#11
January 7, 2015 at 09:58:27
Don't know how to mark this thread as solved, how do I do that?

Report •

Ask Question