Hi,

Try something like this:


Sub test()
Call FindIt(12345, "Mr. C. Smith")
End Sub

Sub FindIt(Findme As String, ReplaceMe As String)

With Worksheets(1).Range("a1:a500")
Findme = 12345
Set c = .Find(Findme, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, 1).Value = ReplaceMe
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


HTH

"Yodaman" wrote:


I'm in the midst of playing with Macros for the first time and have
finding myself getting hopelessly lost.

What I'm attempting to do is create a macro that will replace a value
in a spreadsheet that I'm using to keep customer records. I'll give a
brief example:

In Cell A1, I'll have the number 12345, and in cell B1 will be the name
Mr Smith. The number 12345 correlates specifically to the name Mr Smith.
I want to change that from Mr Smith, to Mr A. Smith, because there are
too many double ups with the name Smith.

Now, what I want to be able to do is create a macro that will be able
to look for every instance of the number 12345, and change the value in
the cell to it's immediate right to Mr A. Smith. The follow on from
this is to be able to do the same to all my numbers and names that I
have in my spread sheet, to permit me to update my records with ease.

I've been attempting to do this with the SUBSTITUTE command to no
avail, and am currently lost in the world of macro jargon.

I would appreciate any help at all in resolving this confusing
problem.

Rgds,

Darren.


--
Yodaman
------------------------------------------------------------------------
Yodaman's Profile: http://www.excelforum.com/member.php...o&userid=28986
View this thread: http://www.excelforum.com/showthread...hreadid=487166