Macro confusion

[Yodaman]

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.

[Toppers]

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
>
>