Finding a cell and replacing it with another word,

obracey756

New Member
Joined
May 28, 2015
Messages
5
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 28, 2015
Messages
5
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You can tell it to temporarily ignore the errors until it finishes with the code, i.e.
Code:
[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]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Glad we could help!:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

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