Help with a loop

arholly · June 26th, 2009, 11:56 AM

Hello:
I would like some help because I'm not that familiar with VBA. What I need to do is create a loop which checks R24, S24, T24,...,AF24 for a given value. If that value is found in that one cell (range), then do a bunch of other stuff.

This is my code, but I don't know how to make it do the loop so it's more effective. Any help would be very greatly appreciated.

Code:

If Range("t24") = "watch" Then
    Range("R25:X25").Select
    ActiveWindow.SmallScroll ToRight:=2
    Range("R25:AJ25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AA24:AF24").Select
    Selection.Copy
    Range("S25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R25:AJ25").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AH24:AM24").Select
    Selection.Copy
    Range("S25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If

Best Regards,
Arholly

Shasur · June 26th, 2009, 09:58 PM

Can you try something like this:

Code:

Sub Check_MultipleRanges()
Dim oRange As Range
Dim oCell As Range
Set oRange = Range("R24, S24, T24, AF24")
For Each oCell In oRange
    If LCase(oCell.Value) = "watch" Then
    
        ' Bunch of Stuff
        MsgBox "Found"
    
    End If
    
Next oCell
End Sub

Cheers
Shasur