Replacing the formula text's substring

[parahumanoid]

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(xlCellTypeFormulas)
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?

 

[Ron Rosenfeld]

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(xlCellTypeFormulas)
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

 

[parahumanoid]

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?

 

[GS]

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. <FYI>Anytime 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.

 

[Ron Rosenfeld]

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.