ammx
02-12-2007, 07:14 AM
Hi,
I'm having problems with the RANGE.REPLACE method when being called in a subroutine.
I have written some VBA code that works fine only when the last FIND/REPLACE action (via the EDIT- FIND/REPLACE menu option) scanned just the active 'SHEET'. However, the same code fails when the last search or replace action scanned the entire WORKBOOK. :(
Using the macro recorder was not of any help either. There is no difference in the recorded code for SHEET or WORKBOOK scans.
I scanned various forums already for this issue, but couldn't find anything that helped me either. :dunno
How can I tell the RANGE.REPLACE method to just use the specified range ("A7") on a specified worksheet (sh)?
I thought the syntax should be:
sh.Range("a7").Replace ...
Attached is a small worksheet that isolates the problem. To try it,
search for something on a single sheet, then press the 3 buttons on sheets 1-3. Now search something in the entire workbook and press the 3 buttons again. You will now see some red error messages...
Please help me,
Michael
P.S.
I use Excel 2002 (10.6823.6817) SP3, multilingual
Doesn't work in English nor in German...
Here is the code:
Public Sub fixThisSheet()
Dim ActualCalcMethod As Variant
On Error GoTo exit_sub
ActualCalcMethod = Application.Calculation
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Call FixFormulas(ActiveSheet)
exit_sub:
Application.StatusBar = "Recalculating all sheets...please wait!"
Application.Calculation = ActualCalcMethod
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.StatusBar = False
End Sub
Public Sub FixFormulas(sh As Worksheet)
'only works well when the last search was by sheet and not by workbook!!!
' by sheet : only the 'TOTAL:' in the actual sheet is replaced
' by workbook: all occurences of 'TOTAL:' are replaced
sh.Range("a7").Replace _
What:="TOTAL:", Replacement:="## ERROR ##", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'restore 'TOTAL:' on actual sheet only
sh.Range("a7") = "TOTAL:"
End Sub
I'm having problems with the RANGE.REPLACE method when being called in a subroutine.
I have written some VBA code that works fine only when the last FIND/REPLACE action (via the EDIT- FIND/REPLACE menu option) scanned just the active 'SHEET'. However, the same code fails when the last search or replace action scanned the entire WORKBOOK. :(
Using the macro recorder was not of any help either. There is no difference in the recorded code for SHEET or WORKBOOK scans.
I scanned various forums already for this issue, but couldn't find anything that helped me either. :dunno
How can I tell the RANGE.REPLACE method to just use the specified range ("A7") on a specified worksheet (sh)?
I thought the syntax should be:
sh.Range("a7").Replace ...
Attached is a small worksheet that isolates the problem. To try it,
search for something on a single sheet, then press the 3 buttons on sheets 1-3. Now search something in the entire workbook and press the 3 buttons again. You will now see some red error messages...
Please help me,
Michael
P.S.
I use Excel 2002 (10.6823.6817) SP3, multilingual
Doesn't work in English nor in German...
Here is the code:
Public Sub fixThisSheet()
Dim ActualCalcMethod As Variant
On Error GoTo exit_sub
ActualCalcMethod = Application.Calculation
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Call FixFormulas(ActiveSheet)
exit_sub:
Application.StatusBar = "Recalculating all sheets...please wait!"
Application.Calculation = ActualCalcMethod
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.StatusBar = False
End Sub
Public Sub FixFormulas(sh As Worksheet)
'only works well when the last search was by sheet and not by workbook!!!
' by sheet : only the 'TOTAL:' in the actual sheet is replaced
' by workbook: all occurences of 'TOTAL:' are replaced
sh.Range("a7").Replace _
What:="TOTAL:", Replacement:="## ERROR ##", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'restore 'TOTAL:' on actual sheet only
sh.Range("a7") = "TOTAL:"
End Sub