easy way to do a find and replace in a column?

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a worksheet with a column of codes like this

aaa
bbb
ccc
ddd
eee

Is there an easy way to do an if statement or something
that if the code is AAA change the AAA in that column to
APPLE and so forth?

without manually doing a find and replace for every one..I
get this report each week and I dont want to do it
manually..

Thanks
Scott
 
This gives me a good start..what does DIM variables mean
though?

Thanks
Scott
-----Original Message-----
Hi Scott

You can create a simple Find/Replace module that you can
Click to expand...
run to take care of your replacements for you.
This is pretty simplified, without getting into Loops and
Click to expand...
stuff. (Be sure to Dim your variables -- it'll makes it
easier down the road...)
Sub FindReplace()
On Error GoTo Err_FindReplace

Dim strFind_a As String, strReplace_a As String

strFind_a = "aaa"
strReplace_a = "APPLE"

Columns("A:A").Select
Selection.Replace What:=strFind_a,
Click to expand...
Replacement:=strReplace_a, LookAt:=xlPart, _
 
It means to declare your variables at the beginning of your code...

(Sorry, I thought I was in the programming section.)

Not sure if you've done any work in VBA, but I would recommend that you create an empty worksheet called something like "Find_Replace_Report_Module.xls".

In your new document:

* Select 'View', then 'Toolbars', then 'Visual Basic'
* Click the 'Visual Basic Editor' Icon
* Past the code that I gave you. Change the Columns("A:A") to reference the column where specified codes reside
* Follow the naming precedence that I've given you to continue declaring your variables (strFind_b, strReplace_b, etc...) You will need to 'Dim', or declare the variables at the beginning of the module, or code, AND you will also need to define the values of the variables: strFind_b = "bbb", strReplace_b = "BANANA", etc...

okey dokey? :)
 
Back
Top