Loop & Find problems

**mohom** · 05-10-2008, 10:34 AM

I am trying to create a loop Macro that will search for the word "pediatric" in Column C of each Row, and when "pediatric" is found, enter "Peds" in Column E of that row.

I created this macro using Record and then added a Loop function, but it is not working. I am getting a Loop Do syntax error when I "step into" the macro. As I'm sure will be obvious, I am very much a beginner with macros.

Can anyone please tell me what mistakes I am making here?

Thanks very much!

Sub LoopPeds()
'
' LoopPeds Macro
' Macro recorded 5/10/2008 by Mohom
'
' Keyboard Shortcut: Ctrl+w

    x = 5
    
    Do While Cells(x, 3).Value <> ""
    Cells.Find(What:="Pediatric", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells(x, 5).Select
    ActiveCell.FormulaR1C1 = "Peds"
    With ActiveCell.Characters(Start:=1, Length:=4).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
            Cells(x, 6).Select
            x = x + 1
        Loop
   

    
End Sub

**royUK** · 05-10-2008, 10:41 AM

Welcome to the Forum.

You need to take the few minutes necessary to read the Forum Rules - see the link below. Your Thread Title needs editing & you haven't used Code tags, I will add these for you now.

**mudraker** · 05-10-2008, 10:41 AM

Welcome to Exceltip forum

Please take a couple of minutes and read the forum rules then edit your thread title by following the instructions in the rules (Rule 1) and wrap your VBA code (Rule 3)

An Announcement thread that covers the rules is at the top of each forum or you may use the link below in my signature.

**royUK** · 05-10-2008, 11:05 AM

Try this

Option Explicit

Sub LoopPeds()
    '
    ' LoopPeds Macro
    ' Macro recorded 5/10/2008 by Mohom
    '
    ' Keyboard Shortcut: Ctrl+w
    Dim rng    As Range
    Dim rng2   As Range
    Dim cl     As Range
    Const str  As String = "Paediatric"


    Set rng = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
    For Each cl In rng
        If cl.Value = str Then
            If rng2 Is Nothing Then
                Set rng2 = cl
            Else: Set rng2 = Union(cl, rng2)
            End If
        End If
    Next cl
    If Not rng2 Is Nothing Then

        rng2.Offset(0, 2).Value = "Peds"
        With rng2.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
    Else: MsgBox "No instances of " & str & " were found"
    End If
    '

End Sub