software

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

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(quot;sheet1quot;)

WhatToFind = Array(quot;123456quot;, quot;XYZABCquot;)
ReplaceWith = Array(15, 15)

If UBound(WhatToFind) lt;gt; UBound(ReplaceWith) Then
MsgBox quot;Design error!quot;
Exit Sub
End If

For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
FirstAddress = quot;quot;
With wks.Range(quot;a:aquot;)
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 lt;gt; FirstAddress
End If
End With
Next iCtr

End Sub

This actually replaces anything that was in column C with that new value.
rojobrown wrote:
gt;
gt; Hello all!!
gt;
gt; I am trying to do a macro that will look for a number in column A and then
gt; replace the contents in column c with a 15.
gt;
gt; Example
gt; A B C
gt; 123456 400
gt; ABC123 400
gt; XYZABC 400
gt; I want it to look for 123456 and XYZABC and replace the 400 with a 15. Is
gt; there anyway to do this in a macro?
gt; --
gt; Thanks a bunch!
gt; rojobrown

--

Dave Peterson


On Error Resume Next
iRow = Application.Match(quot;123456quot;,columns(1),0)
On Error Goto 0
If iRow lt;gt; 0 Then
Cells(iRow,quot;Cquot;).Value = 15
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;rojobrownquot; gt; wrote in message
news
gt; Hello all!!
gt;
gt; I am trying to do a macro that will look for a number in column A and then
gt; replace the contents in column c with a 15.
gt;
gt; Example
gt; A B C
gt; 123456 400
gt; ABC123 400
gt; XYZABC 400
gt; I want it to look for 123456 and XYZABC and replace the 400 with a 15. Is
gt; there anyway to do this in a macro?
gt; --
gt; Thanks a bunch!
gt; rojobrown