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:

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 replace by workbook: all occurrences 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

[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.

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

[Charlize]

This routine will search in a specified range for 'Total'. Probably indeed a 'bug' or malfunctioning.

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

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:

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

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'...

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

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!