Find and replace in selected cell range

seven1four

New Member
Joined
Jan 18, 2019
Messages
7
I would like to find and replace the selected cells on the active sheet. (selected by mouse before a run the macro)
On sheet2 i have a reference table column a is keyword column is replacement.

I found this macro but it replaces the whole active sheet.

Sub Macro1()
Dim i As Integer
Dim FindStr As String
Dim RepStr As String
For i = 1 To 2
FindStr = Sheet2.Range("A" & i).Value
RepStr = Sheet2.Range("B" & i).Value

Sheet1.Cells.Replace What:=FindStr, Replacement:=RepStr
Next i
End Sub

Thank you.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,501
Try...

Code:
Sub Macro1()
    Dim i As Integer
    Dim FindStr As String
    Dim RepStr As String
    Sheet1.Activate
    For i = 1 To 2
        FindStr = Sheet2.Range("A" & i).Value
        RepStr = Sheet2.Range("B" & i).Value
        ActiveWindow.RangeSelection.Replace What:=FindStr, Replacement:=RepStr
    Next i
End Sub

Hope this helps!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top