Solved: Replace in a sheet and not the entire workbook

[ammx]

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.

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:

[VBA]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
[/VBA]

[ammx]

hmmmm....

seems to be more complicated than initially thought.

First I thought it was a problem with passing the sheet object by a subroutine. But this is not the case. After isolating the problem further to the REPLACE method only, I've spotted the same misbehaviour.
The same code behaves differently, just depending on the last FIND or REPLACE action from the EDIT menu.

If last find was by 'sheet' it works as expected.
But if last find was by 'workbook' the code completely ignores any range that I put in front of the REPLACE method. It simply executes the REPLACE method against the whole workbook. I tried all kinds of SHEETS().RANGE().CELLS() combinations in front of .REPLACE. None seems to do it right.

I tested this also on Excel 2003 on several other computers.
It is always the same errorneous behaviour.

However, I need to have some sort of control about this. Ideally, I want to 'reset' the FIND / REPLACE command to search by 'sheet'. Of course, any other idea is highly appreciated.

Please help,

Michael

Note:

When opening the attached Excel file, use menu WINDOWS - ARRANGE - HORIZONTAL to display all three sheets at the same time. This makes it easier to follow up what's going wrong.


Code:

[VBA]Public Sub test()
'Only works when last FIND/REPLACE operation was by 'Sheet'.
'Same code fails, when last FIND/REPLACE was by 'Workbook'.

Worksheets(1).Range("a1") = "Replace 1"
Worksheets(2).Range("a1") = "Replace 2"
Worksheets(3).Range("a1") = "Replace 3"

MsgBox "Click OK to start replacing"

Worksheets(1).Cells.Replace What:="Replace*", Replacement:="This is sheet 1"
Worksheets(2).Cells.Replace What:="Replace*", Replacement:="This is sheet 2"
Worksheets(3).Cells.Replace What:="Replace*", Replacement:="This is sheet 3"
End Sub[/VBA]

[Charlize]

This routine will search in a specified range for 'Total'. Probably indeed a 'bug' or malfunctioning.[vba]Sub test()
Dim rng As Range, result As Range
Dim firstaddress As String, vmessage As String
Dim lrow As Long
lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
Set rng = Worksheets(1).Range("A1:A" & lrow)
vmessage = "Item 'Total' was found on rows :" & vbCrLf
With rng
Set result = .Find("Total", LookIn:=xlValues)
If Not result Is Nothing Then
firstaddress = result.Address
Do
vmessage = vmessage & "- " & result.Row & vbCrLf
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <> firstaddress
End If
End With
MsgBox vmessage, vbInformation, "Requested info ..."
End Sub[/vba]
Charlize

[ammx]

Hi Charlize,

thank you very much for your help. You saved my day

I analyzed your code a bit further and could extract it to a single miracle line that does the job:

[VBA]Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)[/VBA]

With that line just before any REPLACE operations, the EDIT FIND/REPLACE dialog is resetted to look within 'sheet' and not within 'workbook'. So just what I needed.

Here is my code snippet that incorporates the changes. Just watch out for the lines with 'dummy'...

[VBA]Public Sub NowItWorks()
' Now it works, the 'find dummy' ensures that the find is reset to search by 'sheet'
Dim dummy As Range

Worksheets(1).Range("a1") = "Replace 1"
Worksheets(2).Range("a1") = "Replace 2"
Worksheets(3).Range("a1") = "Replace 3"

MsgBox "Click OK to start replacing"

'Reset the EDIT FIND function to 'sheet' instead of 'workbook'
Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

Worksheets(1).Cells.Replace What:="Replace*", Replacement:="This is sheet 1"

Worksheets(2).Cells.Replace What:="Replace*", Replacement:="This is sheet 2"

Worksheets(3).Cells.Replace What:="Replace*", Replacement:="This is sheet 3"
End Sub[/VBA]

Special thanks also from 'Kalle', our dog. He is from Belgium and sends warm greetings back home .

Michael

P.S.

I still believe that this nasty behaviour is a real bug in the Excel VBA. However, this work around is acceptable to me.

[lucas]

Could it be a bug in Excel?

that's usually it.

[Kaahto]

Just registered to say that in 2016, a decade later, this is still actual and the dummy Find solved the issues I was having.
Thanks!