VBA using input boxes to find and replace

[Access Beginner]

Hi all,

I recorded a macro for a find and replace and was hoping if someone could tweak so that the "What" is and input box and the "Replacement" is also an input box

Sub Find_and_Replace()
'
' Find_and_Replace Macro
'
'
    Sheets("Data Table3").Select
    Range("D3:D18").Select
    Selection.Replace What:="20131227", Replacement:="20140627", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[dmt32]

Try this:

Sub Find_and_Replace()




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


    Title = Array("Search", "Replace")
    i = 0
    Do
        sInput(i) = InputBox("What is the " & Title(i) & " Value?", Title(i))
        If StrPtr(sInput(i)) = 0 Then Exit Sub
        If Len(sInput(i)) = 0 Then
            MsgBox "You Must Enter a " & Title(i) & " Value.", 48, "Entry Required"


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


    Sheets("Data Table3").Range("D3:D18").Replace What:=sInput(0), _
                                                  Replacement:=sInput(1), _
                                                  LookAt:=xlPart, _
                                                  MatchCase:=False
End Sub

Dave

 

[Access Beginner]

thanks Dave, I'll try it out Monday.

 

[Access Beginner]

Thanks Dave, worked a treat

Cheers
Haydn

 

[MBAhelp]

Hello!
This worked for me! I need to add in a button on the sheet to make this input box come up and add a message after the replace is done. Any ideas on how to do this?