For each loop not making changes to all sheets?

Question

I am learning excel vba through an online course.

One of the exercises requires that you you find and replace text in a selected cell for all sheets. Here is my attempt at a solution using a For each loop.

Sub Find_Replace_Selected_Value()
' Initialize Variables
Dim inputRange As Range
Dim searchString As String
Dim msgboxResp As VbMsgBoxResult
Dim msgboxRespAll As VbMsgBoxResult
Dim myWorkSheet As Worksheet

' Ask The user if they want the word replaced in all sheets.
msgboxRespAll = MsgBox("Would you like to apply changes to all sheets?", vbYesNoCancel, "Apply to All")

Select Case msgboxRespAll
    ' if the user only want to apply changes to current sheet.
    Case vbNo
        ' Ask User to select a range to search for.
        Set inputRange = Application.InputBox("Please select a range with a value to find and replace.", "Find", , , , , , 8)
        ' Ask User to input a value to replace with.
        searchString = InputBox("What would you like to replace " & inputRange.Value & " with?", "Replace")
        ' Perform Find and Replace
        Call Cells.Replace(inputRange.Value, searchString)
        ' Ask User to Repeat
        msgboxResp = MsgBox("Would you like to find and replace again?", vbYesNo, "Repeat")
        ' If yes, tell user that it's a future update.
        If msgboxResp = vbYes Then
        Call MsgBox("This functionality is scheduled for a later release.", vbOKOnly, "Not Available")
        End If
    ' if the user wants to apply changes to all sheets.
    Case vbYes
        ' Ask User to select a range to search for.
        Set inputRange = Application.InputBox("Please select a range with a value to find and replace.", "Find", , , , , , 8)
        ' Ask User to input a value to replace with.
        searchString = InputBox("What would you like to replace " & inputRange.Value & " with?", "Replace")
        ' Perform Find and Replace
        For Each myWorkSheet In Sheets()
            myWorkSheet.Activate
            Call Cells.Replace(inputRange.Value, searchString)
            Next

            ' Ask User to Repeat
            msgboxResp = MsgBox("Would you like to find and replace again?", vbYesNo, "Repeat")
            ' If yes, tell user that it's a future update.
            If msgboxResp = vbYes Then
            Call MsgBox("This functionality is scheduled for a later release.", vbOKOnly, "Not Available")
            End If


End Select


End Sub

This code replaces the value for only one sheet.

Can you please help my figure out why this method is not working? The solution is completely different to I cannot get an answer from that.

[Don't use `Activate` or `Select`](https://stackoverflow.com/q/10714251/4088852) - read that entire thread. You have a `Worksheet` reference, so you should be using that to qualify `Cells.Replace` - `myWorkSheet.Cells.Replace`. 2 unrelated things: The `Call` keyword is completely unnecessary (it's a hold-over from prior basic versions from like the 1980s). Also, I'd get in the habit of [indenting your code correctly](http://rubberduckvba.com/Indentation). — Comintern, Jan 13 '19 at 17:08

score 0 · Answer 1 · answered Jan 13 '19 at 18:00

Spare Another Variable

For the multi sheet version, declare another variable to hold the initial value of the input range.

The Code

Sub Find_Replace_Selected_Value()

    ' Initialize Variables
    Dim inputRange As Range
    Dim searchString As String
    Dim replaceString As String
    Dim msgboxResp As VbMsgBoxResult
    Dim msgboxRespAll As VbMsgBoxResult
    Dim myWorkSheet As Worksheet

    ' Ask The user if they want the word replaced in all sheets.
    msgboxRespAll = MsgBox("Would you like to apply changes to all sheets?", _
            vbYesNoCancel, "Apply to All")

    Select Case msgboxRespAll
        ' if the user only wants to apply changes to current sheet.
        Case vbNo
            ' Ask User to select a range to search in.
            Set inputRange = Application.InputBox("Please select a range " _
                    & "with a value to find and replace.", "Find", , , , , , 8)
            ' Ask User to input a value to replace with.
            replaceString = InputBox("What would you like to replace " _
                    & inputRange.Value & " with?", "Replace")
            ' Perform Find and Replace
            Cells.Replace inputRange.Value, replaceString
            ' Ask User to Repeat
            msgboxResp = MsgBox("Would you like to find and replace again?", _
                    vbYesNo, "Repeat")
            ' If yes, tell user that it's a future update.
            If msgboxResp = vbYes Then
                MsgBox "This functionality is scheduled for a later release.", _
                        vbOKOnly, "Not Available"
            End If
        ' if the user wants to apply changes to all sheets.
        Case vbYes
            ' Ask User to select a range to search in.
            Set inputRange = Application.InputBox("Please select a range " _
                    & "with a value to find and replace.", "Find", , , , , , 8)
            ' Ask User to input a value to replace with.
            replaceString = InputBox("What would you like to replace " _
                    & inputRange.Value & " with?", "Replace")
            ' Assign the value from inputRange to a variable.
            searchString = inputRange.Value
            ' Perform Find and Replace
            For Each myWorkSheet In Worksheets
                myWorkSheet.Cells.Replace searchString, replaceString
            Next
            ' Ask User to Repeat
            msgboxResp = MsgBox("Would you like to find and replace again?", _
                    vbYesNo, "Repeat")
            ' If yes, tell user that it's a future update.
            If msgboxResp = vbYes Then
                MsgBox "This functionality is scheduled for a later release.", _
                        vbOKOnly, "Not Available"
            End If
    End Select

End Sub

score -1 · Answer 2 · answered Jan 13 '19 at 17:33

The problem is with the range variable inputRange. You are setting this as range reference for the active sheet. Therefore, when it is looping through each sheet it is referencing that range for each of the sheets. So in effect, you are replacing each value on each sheet with the same thing.

I would change the inputRange from a range variable to a simple string variable. This way it will be static for each loop. So just change inputRange to inputString and remove the "set" keyword when getting the value from the inputbox.

Try This:

Sub Find_Replace_Selected_Value()
' Initialize Variables
Dim inputRange As Range
Dim inputString as String
Dim searchString As String
Dim msgboxResp As VbMsgBoxResult
Dim msgboxRespAll As VbMsgBoxResult
Dim myWorkSheet As Worksheet

' Ask The user if they want the word replaced in all sheets.
msgboxRespAll = MsgBox("Would you like to apply changes to all sheets?", vbYesNoCancel, "Apply to All")

Select Case msgboxRespAll
    ' if the user only want to apply changes to current sheet.
    Case vbNo
        ' Ask User to select a range to search for.
        Set inputRange = Application.InputBox("Please select a range with a value to find and replace.", "Find", , , , , , 8)
        ' Ask User to input a value to replace with.
        searchString = InputBox("What would you like to replace " & inputRange.Value & " with?", "Replace")
        ' Perform Find and Replace
        Call Cells.Replace(inputRange.Value, searchString)
        ' Ask User to Repeat
        msgboxResp = MsgBox("Would you like to find and replace again?", vbYesNo, "Repeat")
        ' If yes, tell user that it's a future update.
        If msgboxResp = vbYes Then
        Call MsgBox("This functionality is scheduled for a later release.", vbOKOnly, "Not Available")
        End If
    ' if the user wants to apply changes to all sheets.
    Case vbYes
        ' Ask User to select a range to search for.
        inputString = Application.InputBox("Please select a range with a value to find and replace.", "Find", , , , , , 8)
        ' Ask User to input a value to replace with.
        searchString = InputBox("What would you like to replace " & inputString & " with?", "Replace")
        ' Perform Find and Replace
        For Each myWorkSheet In Sheets()
            myWorkSheet.Activate
            Call Cells.Replace(inputString, searchString)
            Next

            ' Ask User to Repeat
            msgboxResp = MsgBox("Would you like to find and replace again?", vbYesNo, "Repeat")
            ' If yes, tell user that it's a future update.
            If msgboxResp = vbYes Then
            Call MsgBox("This functionality is scheduled for a later release.", vbOKOnly, "Not Available")
            End If


End Select


End Sub

For each loop not making changes to all sheets?

2 Answers2

Spare Another Variable

The Code