Find and Replace Macro Not Working

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
This macro was posted in MrExcel some time ago and I found it on the internet in a search. I tried running it but it doesn't work. Any suggestions please?

Thank you,

Jared Z.

Code:
Sub Find_and_Replace()

Dim myList, myRange
    Set myList = Sheets("Run Macros").Range("F1:G13") 'two column range where find/replace pairs are
    Set myRange = Sheets("Run Macros").Range("J2:R11") 'range to be searched
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
    Next cel
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
I click "Run" after I select the macro and the values that I want to change in range J2:R11 remain the same. The "Find values are in column "F" and the replace values are in column "G".

Thank you,

JZ
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
Are you trying to do an exact match, or a partial match?
Also does it need to be case sensitive?
Finally, are the values you are trying to change, hard values or the result of a formula?
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70

ADVERTISEMENT

I ran the macro again today and it seems that it is working. However, in the first column (let's say it has a cell reference to column "I") I want to replace that reference with column "Q". It replaces that reference. When it get's to the ninth column, it may already be a reference to column "Q" so that when it changes that reference to column "Y" it will change the first column reference to column "Y" even though it should be a reference to column "Q". Is there a way around this?

Thank you,

Jared Z.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
I've absolutely no idea because I don't know what you are trying to do. ;)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,610
Members
430,557
Latest member
MK15

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