Find and Replace using Macros

[MBAhelp]

Hi! I'm trying to do this in Excel using Macros. I can't find any code to do this:

• The user should be able to push a button on the Large Dataset tab that brings up a user input box. (Hint: Learn how to do this by watching the tutorial video on creating a button)
• The macro should prompt the user to update a field (any field), based on the user’s input on the following items:
• The old text that you would like to change (using an InputBox) (Hint: Learn how to do this by watching the tutorial video on InputBox)
• The new text that you would like to have instead (using another InputBox)
• The macro should then search for the old text, and replace it with the new text (Hint: Record this macro step)
• When the macro finishes running, please pop a message box telling the user that the job is done (using a MsgBox) (Hint: Learn how to do this by watching the tutorial video on MsgBox)
• Associate the macro to a button, so the user can run the macro with the click of a button.

I should be able to do this using the button:

• Johnny Robertson’s new last Name: Robertson‐Casey
• Emilio quit his job and now Allison has to take over his clients.
• All Chevy trucks are now replaced by Chrysler vans

Thank you!

 

[Jimmypop]

Hi there

Try the following suggestion by dmt32... VBA using input boxes to find and replace

You might need to tweak your sheet name and range references...

 

[MBAhelp]

That did work!

Any idea how I can make make a push button that will bring up the input box?

 

[Jimmypop]

Will have a look when back at PC .

 

[MBAhelp]

Thank you!
I also need a message box to come up after to display a "the job is done" message

 

[MBAhelp]

I figured it all out!

In case anyone else needs it
Sub Find_and_Replace()


Dim Title As Variant
Dim sInput(2) As Variant
Dim i As Integer


Title = Array("is the old text you'd like to change?", "would you like to replace it with?")
i = 0
Do
sInput(i) = InputBox("What " & Title(i))
If StrPtr(sInput(i)) = 0 Then Exit Sub
If Len(sInput(i)) = 0 Then
MsgBox "You must enter a value.", 48, "Entry Required"


Else
i = i + 1
End If
Loop Until i > 1

MsgBox "The job is done!"


Sheets("Large Dataset").Range("A1:S450").Replace What:=sInput(0), _
Replacement:=sInput(1), _
LookAt:=xlPart, _
MatchCase:=False
End Sub

Then add the button: