Dynamic Find and Replace

[TheRetroChief]

Greetings all,

Is there a way in which you can use two cells (a2 & b2 for example) with a button to trigger a macro which will search for a2 and replace with b2 over a certain range of cells? unless there is a function to do it of course, which i would prefer!!

Ta muchly,
Ali Mac

[MatrixMan]

Sure is; you have two simple options -

1. take a look at the Find function; this is directly out of the online help:

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Obviously, you'd just make the "2" the value of A2 and "5" the value of B2 in your example.

2. use the Replace function:

    Cells.Replace What:="asdf", Replacement:="qwerty", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Hope that helps.

[TheRetroChief]

Ah, but i want A2 and B2 to change without having to change the macro. Will this accept that? i.e. 1 is "A2" and 2 is "B2"???

[Shijesh Kumar]

Try this code

Sub find_and_replace()

    Selection.Replace What:=Range("A2"), Replacement:=Range("B2"), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End Sub

Note... First select the range where u want to find and replace and then run the macros..

[MatrixMan]

Thanks Shijesh So Retro - the answer to your question is "yes"; just replace my "asdf" and "qwerty" with the range values as Shijesh has shown below.

[TheRetroChief]

Cheers guys.