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 SubIf 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.
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...
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.
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.
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.
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?
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.
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.
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
I only see #VALUE, nothing else.
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.
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...
Ad Choices