Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
An "If" statement coded in a macro
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
An "If" statement coded in a macro
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
An "If" statement coded in a macro
On Error Resume Next iRow = Application.Match("123456",columns(1),0) On Error Goto 0 If iRow < 0 Then Cells(iRow,"C").Value = 15 End If -- HTH Bob Phillips (remove nothere from email address if mailing direct) "rojobrown" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
how to create automatic macro with if statement or similar | Excel Discussion (Misc queries) | |||
Run Macro From If Statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) |