Finding a cell and replacing it with another word,

[obracey756]

Hi All,

So i'm trying to do something which at first I thought would be really simple but is actually proving itself difficult to my little brain.

I'm trying to search column A for a word (lets say "Test1")
If it finds the word Test 1 then I want to replace that Test1 with a number (lets say 1234)
If it can't find it then it just moves on to look for the next word (lets say Test2)
And so on....

Any help to get me started would be really appreciated! seem to be going round in circles

Thanks muchly!

Ollie

 

[Joe4]

How many of these words do you need to look for/replace?

If just a few, Find & Replace should work. If you use the Macro Recorder, you can actually record VBA code that would do this for you.

 

[obracey756]

Hi Joe4

Thanks for the help,

I used the macro recorder to create the following

Sub ReplaceText()
'


Cells.Find(What:="TEST123", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="TEST123", Replacement:="1111", LookAt:=xlPart _
, SearchOrder:=xlByRows
Cells.Find(What:="TEST456", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="TEST456", Replacement:="2222", LookAt:=xlPart _
, SearchOrder:=xlByRows
End Sub

Only sticking point is that if the word "TEST123" doesn't exist on the worksheet it throws up the debug window, when really I could do with the code just moving on to try find the next word (in this example that would be TEST456).

The reason the code wont always be able to find the word (TEST123) in the workbook is because although this code will hopefully eventually hold over a 100+ items in it, the workbook its run on might only have 2 of those items but sometimes it will have 90+ of these items.

Thats probably an awful explanation...but I hope it clear up what i'm trying to do a bit

Ollie

 

[Joe4]

You can tell it to temporarily ignore the errors until it finishes with the code, i.e.

[COLOR=#333333]Sub ReplaceText()[/COLOR]
[COLOR=#333333]'[/COLOR]
On Error Resume Next

[COLOR=#333333]Cells.Find(What:="TEST123", After:=ActiveCell, LookIn:=xlFormulas, _[/COLOR]
[COLOR=#333333]LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _[/COLOR]
[COLOR=#333333]MatchCase:=False, SearchFormat:=False).Activate[/COLOR]
[COLOR=#333333]ActiveCell.Replace What:="TEST123", Replacement:="1111", LookAt:=xlPart _[/COLOR]
[COLOR=#333333], SearchOrder:=xlByRows[/COLOR]
[COLOR=#333333]
Cells.Find(What:="TEST456", After:=ActiveCell, LookIn:=xlFormulas, _[/COLOR]
[COLOR=#333333]LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _[/COLOR]
[COLOR=#333333]MatchCase:=False, SearchFormat:=False).Activate[/COLOR]
[COLOR=#333333]ActiveCell.Replace What:="TEST456", Replacement:="2222", LookAt:=xlPart _[/COLOR]
[COLOR=#333333], SearchOrder:=xlByRows[/COLOR]
[COLOR=#333333]
On Error Goto 0

End Sub[/COLOR]

 

[obracey756]

Well that worked like a treat! Thanks so much for your help!

Ollie

 

[Joe4]

You are welcome. Glad we could help!