Replace values with VBA and show with a MsgBox how many cells were changed-CodePudding

I'm trying to make a code to search and replace some values with an input. I'm stuck trying to figure out how to prompt a MsgBox showing how many cells were changed.

Thanks!

Sub find_and_replace_loop()

Dim find_value, replace_value As String

On Error Resume Next

find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")

i = 0
For Each Rng In Worksheets("Tabelle1").Range("A1:A23")
 
    If Rng.Value Like "*" & find_value & "*" Then
        Rng.Offset(0, 1).Value = replace_value
    End If
    i = i   1 'counts changes
Next

MsgBox i

End Sub

CodePudding user response：

You have to increase i only in case the product was found, i.e.

    If Rng.Value Like "*" & find_value & "*" Then
        Rng.Offset(0, 1).Value = replace_value
        i = i   1 'counts changes
    End If

Apart from that there might possibly be more effective ways to do what you do with your code like Range.replace

CodePudding user response：

Ok thanks, now it works. As u can see, i am new with VBA so, probably there are many ways to make it better. But it works and the code is not too long, so it's ok i guess. Is there any way to set a formula as replace_value? For example

EDIT. Ok I did it adding the formula here but I have a problem with the formula. I want to make something like "=foundcell foundcell.Offset(0,9)" for each cell

Rng.Offset(0, 9).Formula =......

Thanks!