One way:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim FoundCell As Range
Dim FirstAddress As String
Dim WhatToFind As Variant
Dim ReplaceWith As Variant
Dim iCtr As Long

Set wks = Worksheets("sheet1")

WhatToFind = Array("123456", "XYZABC")
ReplaceWith = Array(15, 15)

If UBound(WhatToFind) < UBound(ReplaceWith) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
FirstAddress = ""
With wks.Range("a:a")
Set FoundCell = .Cells.Find(what:=WhatToFind(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 2).Value = ReplaceWith(iCtr)
Set FoundCell = .FindNext(FoundCell)
Loop While FoundCell.Address < FirstAddress
End If
End With
Next iCtr

End Sub

This actually replaces anything that was in column C with that new value.



rojobrown wrote:

Hello all!!

I am trying to do a macro that will look for a number in column A and then
replace the contents in column c with a 15.

Example
A B C
123456 400
ABC123 400
XYZABC 400
I want it to look for 123456 and XYZABC and replace the 400 with a 15. Is
there anyway to do this in a macro?
--
Thanks a bunch!
rojobrown

--

Dave Peterson