How combine Cells.Find with If Then in VBA

November 14, 2010 at 19:47:27
Specs: Macintosh
I'm a newbie to Visual Basic and I'm having difficulty putting together a macro in Excel 2011 for Mac. Essentially, I'm using a macro to enter in several rows of data if a particular phrase is listed in a column. I'm trying to combine the Cells.Find function with an If Then statement. This is what I have so far:

Sub RaceA1()
'
' RaceA1 Macro
'
'
If Cells.Find("Race-A1") Then
ActiveCell.FormulaR1C1 = "Race-A1"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Transition-A1-wk1"
ActiveCell.Offset(-2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Peak-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Peak-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build2-A1-wk4"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build2-A1-wk3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build2-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build2-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build1-A1-wk4"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build1-A1-wk3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build1-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Build1-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Transition-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base3-A1-wk4"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base3-A1-wk3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base3-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base3-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base2-A1-wk4"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base2-A1-wk3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base2-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base2-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base1-A1-wk4"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base1-A1-wk3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base1-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Base1-A1-wk1"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Prep-A1-wk4"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Prep-A1-wk3"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Prep-A1-wk2"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Prep-A1-wk1"
ActiveCell.Offset(1, 0).Range("A1").Select
End If
End Sub

Any suggestions? The code above gives me an error when I try to add the Cells.Find and If then statements. Thank you in advance.


See More: How combine Cells.Find with If Then in VBA

Report •


#1
November 15, 2010 at 05:32:12
You'd be amazed at what you can find in the VBA Help files.

I don't have 2011 for Mac, so I don't know about the Helps files on a Mac, but the standard (PC) method of using .Find is found in the VBA Help files.

Example
This example finds all cells in the range A1:A500 on worksheet 
one that contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

P.S. Rarely do you have to Select an object in VBA in order to perform an operation on it.

This:

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Transition-A1-wk1"

Can be reduced to this:

ActiveCell.Offset(1, 0).FormulaR1C1 = "Transition-A1-wk1"

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •
Related Solutions


Ask Question