Hello everybody!
The VBA code I have at the moment changes "L15" to "$L$15" in cells "A1" and "B1".
I need ?L15? to have an absolute reference, also, in other cells (A2:B3), and when it is not possible, go for a mixed reference (e.g. in cell A2: ?=L15+ML15? for ?$L$15+ML$15?).
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by 6StringJazzer; 09-12-2023 at 08:40 AM. Reason: added code tags per guideline #2; formatted grid
Welcome to the Forum Josecarlospdcj!
First, your question is not clear because your code does not just change A1 and B1, it changes all occurrences of L15 to $L$15 in the whole range you have shown. Also I don't know what you mean by "when it is not possible". Are you trying to change L15 without changing things like ML15, because M$L$15 is illegal?
Perhaps it would be clearer if you described your overall project and what you are trying to achieve.
Also you do not need to activate objects to work on them, and unqualified objects will be assumed to be in ThisWorkbook. This code is equivalent to yours:
Please Login or Register to view this content.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Possibly...Please Login or Register to view this content.
Thank you for the information about improvements to the VBA code.
Sorry! I forgot to mention that there are formulas with more than 256 characters and that I have already tried some Excel add-ins.
Trying to explain better.
I have the following cells:
A B C
1 =L15+L16 =L15+L16 =M15+M16
2 =L15+ML15 =L15+TL15 =M16+M17
3 =L15+L16 =L15+M16 =M17+M18
When activating the macro I get this result:
A B C
1 =$L$15+L16 =$L$15+L16 =M15+M16
2 =L15+ML15 =L15+TL15 =M16+M17
3 =L15+L16 =L15+M16 =M17+M18
I need to achieve the following solution:
L15 to $L$15 in cells: A1:B3
L15 to L$15 in cells: A2 e B2
A B C
1 =$L$15+L16 =$L$15+L16 =M15+M16
2 =$L$15+ML$15 =$L$15+TL$15 =M16+M17
3 =$L$15+L16 =$L$15+M16 =M17+M18
Thank you to everyone who is looking to help.
I don't know why your original code only changes the first row. This is a brute-force approach that works:
Please Login or Register to view this content.
Forgive me if the question is unreasonable.
Could it be that the macro is stopping because it cannot make an absolute reference in the formula in cell A2, precisely in “ML15”, and cannot automatically jump to cell “B2” and following, where there is no such impediment?
That is very likely what it happening. I tested it with different formulas and it does work for all cells. This is not well documented by Microsoft.
For the formula =L15+ML15, the Replace command will change the formula to =$L$15+M$L$15.
Artik
A slip on my part.
After I submitted my post, I saw your new code. So, I realized that the progress was enormous.
Only the formulas for A2 and B2 were not changed.
A2 to “=$L$15+ML$15”
B2 to “=$L$15+TL$15”
Can you continue helping me?
It actually won't. M$L$15 is an illegal reference so it fails. Oddly the Replace method does not raise an error for creating an ill-formed formula (unlike other kinds of attempts to create an ill-formed formula). In the original code, the Replace command just stops when it hits an error and does not work on any other cells in the range.Originally Posted by Artik
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
I am providing the link becuse you are a new member: https://www.mrexcel.com/board/thread.../#post-6104926
There is nothing like this in the description of what you wanted. You are going to have to give a more detailed and rigorous specification of what you need.
Piggybacking on your code, I was able to get closer to what I needed.
VBA code
With the first two lines of code I was able to change “=L15+ML15” to “=L$15+ML$15” and “=L15+TL15” to “=L$15+TL$15”Please Login or Register to view this content.
In the third line of the code I tried, but "=L$15+ML$15" did not change to "=$L$15+ML$15" and “=L$15+TL$15” did not give the result “=$L$15+TL$15”
I am quite hopeful.
Last edited by 6StringJazzer; 09-16-2023 at 10:16 PM. Reason: added code tags per guideline #2
Administrative Note:
Please include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you for the message.
I will try to use these resources.
As for the cross-posting, I clearly understood what I should have done and why I won't be a repeat offender.
I sincerely apologize for what happened.
I seem to have found a solution. But it is not universal. It is prepared to solve only this problem.
The idea of the macro is to go through the cells of the indicated range twice. In the first loop we swap only the longer addresses, and in the second loop we swap the shorter ones.
ArtikPlease Login or Register to view this content.
Yes, I meant that there will be an attempt to replace the formula (which of course fails). But I am surprised by the performance of the Replace method. I never paid attention to this problem.
Artik
Josecarlospdcj,
Select the cell(s) that you want to convert.
Tested the code and it is working as you explained.
Please Login or Register to view this content.
I never did either, until now. If you tried to make a direct assignment of that formula to the Formula property, it would raise a runtime error (1004 I think). But if Replace encounters an error, it is dangerously silent.
jindon, Ah, the power of RegEx.
It seems that a few more screws need to be adjusted.
1. for the formula:does not change the second occurrence of L15Please Login or Register to view this content.
2. oddly enough, for the formulathe address of ML15 is swapped twicePlease Login or Register to view this content.
3. for formuladoes not change L15Please Login or Register to view this content.
4. sometimes errors in the linewhich I changed toPlease Login or Register to view this content.ArtikPlease Login or Register to view this content.
Last edited by Artik; 09-16-2023 at 10:41 PM.
If OP asks in that way, I will change my code, but OP needs;
What else can anyone GUESS?
I'm afraid this is just the beginning. OP mentions:ArtikI forgot to mention that there are formulas with more than 256 characters
Josecarlospdcj,
e.g
Convert
=TT5*L5/(ML5+100)&"this is XL12"&XL5&"LL15"
to
=TT5*$L$5/(ML$5+100)&"this is XL12"&XL$5&"LL15"
Select the cell(s) that you want to convert.
In case this doesn't work for any particular formula, I need to see them.Please Login or Register to view this content.
Artik, thank you immensely for your interest!
I tested your code “Test_Art” and was able to observe the following:
It works:
Does not change cells with text, without formulas.
Does not change empty cells.
Only put dollar signs in formulas.
It does not change what is written in quotation marks.
Changes cells with less or more than 256 characters.
It partially works:
Changes "=L15+ML15" to "=L15+ML$15", including in the 1st cell with more than 256 characters.
Changes "=L15+TL15" to "=L15+TL$15", including in the 1st cell with more than 256 characters.
Changes only to mixed reference in lines. Even so, it only fixes the lines if "L15" is preceded by a letter other than "L" (example: Does not change "LL15" to LL$15).
It does not work:
Does not change "=L15+ML15" to "=$L$15+ML$15", even in cells with more than 256 characters.
Does not change "=L15+TL15" to "=$L$15+TL$15", even in cells with more than 256 characters.
Does not change "=L15+L16" to "=$L$15+L16", even in cells with more than 256 characters.
Does not change "=L15+M16" to "=$L$15+M16", even in cells with more than 256 characters.
Does not traverse all cells.
Does not fix the formula columns.
It gives "Run-time error "1004": Application definition or object definition error." in "For Each rngRef In Cell.Precedents"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks