Excel - Find and Replace Macro?

[bloodline76]

Anyone help out with a macro? I need to be able to find and replace multiple words in an excel document.

Eg: Find: 501800 Replace: Made up text message

There are about 50 different words that need finding and replacing with text messages.

Thanks

 

[Knubje]

Don't know about any specific macro, sorry.

Is it find/replace all? If so, not wanting to sound like a tit, but surely doing a find/replace all on 50 numbers/words will take about 10 minutes of your time?

 

[bloodline76]

It would take a very long time. It's a lot more complicated than just finding and replacing. Plus it is a daily job and I am not the only one who has to do it, so it would be quicker doing this as a macro.

 

[A[L]C]

If you can be more specific I can help... ie an example spreadsheet and exactly what you want it to do.

Else, record a macro, Alt + F11, look at the recorded code and work from there

 

[bsoltan]

Public Sub FindReplace()

Dim rng1 As Range
Dim strFindText As String
Dim strReplaceText As String

For Each rng1 In Sheet2.Range("A1:A10")
    strFindText = rng1.Text
    strReplaceText = rng1.Offset(0, 1).Text
    
    Sheet1.Cells.Replace What:=strFindText, Replacement:=strReplaceText, Lookat:=xlPart, _
    Searchorder:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Next rng1

End Sub

A	1
B	2
C	3
D	4
E	5
F	6
G	7
H	8
I	9
J	10

Copy and paste part 2 onto cell A1 in sheet2
Run the macro from a module or sheet in Excel and it will replace any character on sheet1 with the value from that table, so if it finds an A it will replace it with a 1.
You can change the Range it pulls information from and the values but this was just a test to see if it works, if you copy a string of text onto sheet1 and run the macro it will replace the text.

I did the above and:

It would take a very long time. It's a lot more complicated than just finding and replacing. Plus it is a daily job and I am not the only one who has to do it, so it would be quicker doing this as a macro.

became:

9t woul4 t1k5 1 v5ry lon7 t9m5. 9t's 1 lot mor5 3ompl931t54 t81n 10ust 69n49n7 1n4 r5pl139n7. Plus 9t 9s 1 419ly 10o2 1n4 9 1m not t85 only on5 w8o 81s to 4o 9t, so 9t woul4 25 qu93k5r 4o9n7 t89s 1s 1 m13ro.

edit: To make it work over an entire document you'll just need to add another loop to seach all sheets.

edit: As A[L]C says, the replace part is just a copy of recording a macro of the Find-ReplaceAll you can do manually. The added bit is just putting it into a loop to pickup the values you want to replace and what to replace them with, then running through all the values for all the entire sheet.

 

[bloodline76]

Thanks I will try that out