Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the formula text's substring
Hi.
My apologies for the cross-post, original was mistakenly placed into an italian-speaking group (I thought "it" stood for IT, turned out to be a locale name). Can someone post a VBA snippet for Excel 2007 that replaces the requested substring in formulas with the provided string value. The point is that if on a sheet I have a great number of cells where the formula says"=july!whatever", referring to a sheet in another workbook, and I make a copy of it for august, I don't want to hand- edit these formulas. I want to run a macro that will request what to replace (in this case "july") with what ("august"), so that the result is "=august!whatever". I attempted the following: ============================================== Sub ChangeFormulas() Dim str1 As String Dim str2 As String Dim cur As Range Dim result As Boolean str1 = InputBox("Enter original string") str2 = InputBox("Enter replacement string") Application.ScreenUpdating = False For Each cur In ThisBook.ActiveSheet.UsedRange.SpecialCells(xlCell TypeFormulas) result = cur.Replace(str1, str2) If result = False Then MsgBox ("Not changed") End If Next End Sub ================================================== == The message box never fires, but the replacement doesn't occur, all formulas stay intact. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the formula text's substring
On Thu, 6 Oct 2011 23:38:46 -0700 (PDT), parahumanoid wrote:
Hi. My apologies for the cross-post, original was mistakenly placed into an italian-speaking group (I thought "it" stood for IT, turned out to be a locale name). Can someone post a VBA snippet for Excel 2007 that replaces the requested substring in formulas with the provided string value. The point is that if on a sheet I have a great number of cells where the formula says"=july!whatever", referring to a sheet in another workbook, and I make a copy of it for august, I don't want to hand- edit these formulas. I want to run a macro that will request what to replace (in this case "july") with what ("august"), so that the result is "=august!whatever". I attempted the following: ============================================== Sub ChangeFormulas() Dim str1 As String Dim str2 As String Dim cur As Range Dim result As Boolean str1 = InputBox("Enter original string") str2 = InputBox("Enter replacement string") Application.ScreenUpdating = False For Each cur In ThisBook.ActiveSheet.UsedRange.SpecialCells(xlCel lTypeFormulas) result = cur.Replace(str1, str2) If result = False Then MsgBox ("Not changed") End If Next End Sub ================================================= === The message box never fires, but the replacement doesn't occur, all formulas stay intact. What am I doing wrong? How have you declared ThisBook? Your routine works OK here once I replaced that with ThisWorkBook |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the formula text's substring
It's only a matter of my poor translation. I'm using a Russian
localized version, so there is another variable name used instead of ThisWorkBook. And it apparently "kind of" works because if I put a MsgBox inside the loop it fires the expected number of times (the number of formula cells on the active sheet), but the replacement, regardless of the "True" state of the Replace function's result, does not occur. Could it be that I never turned the screenupdating back on? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the formula text's substring
on 10/8/2011, parahumanoid supposed :
It's only a matter of my poor translation. I'm using a Russian localized version, so there is another variable name used instead of ThisWorkBook. And it apparently "kind of" works because if I put a MsgBox inside the loop it fires the expected number of times (the number of formula cells on the active sheet), but the replacement, regardless of the "True" state of the Replace function's result, does not occur. Could it be that I never turned the screenupdating back on? ScreenUpdating will get turned back on when the procedure that turned it off ends. This means the original procedure not sub procedures called by it. In any case, it's always good to have the procedure that turns ScreenUpdating off to also 'explicitly' turn it back on as good programming practice. <FYIAnytime we leave things up to VB[A] to do implicitly it requires extra processing by VB[A} to evaluate what action to take. Better to save that extra processing and just tell VB[A} what to do. Might not seem a big deal on a one-by-one basis but it's the cummulative effect over the entire project that's the concern here. 3 Simple rules: If your code creates objects, your code should explicitly destroy them when no longer needed. If your code changes Excel settings it should explicitly restore them before closing. If your code modifies menus/toolbars it should remove those modifications before closing. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the formula text's substring
On Fri, 7 Oct 2011 22:27:06 -0700 (PDT), parahumanoid wrote:
It's only a matter of my poor translation. I'm using a Russian localized version, so there is another variable name used instead of ThisWorkBook. And it apparently "kind of" works because if I put a MsgBox inside the loop it fires the expected number of times (the number of formula cells on the active sheet), but the replacement, regardless of the "True" state of the Replace function's result, does not occur. Could it be that I never turned the screenupdating back on? I think that would be unlikely. Screenupdating will get turned back on when you exit the Macro anyway. Check to ensure that you are really using the Replace method of the Range object in Russian. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I concatenate text to keep the text's original color/font/s | Excel Discussion (Misc queries) | |||
Replacing formula | Excel Programming | |||
Replacing Formula | Excel Programming | |||
Replacing a value from a formula | Excel Programming |