VBA: Loop Through a Range

August 14th, 2004 | Categories: VBA | Tags: , ,
-->

If you want to test a condition for each cell in a range using VBA, the best way is to loop through the range, testing each cell. Here are two code examples to demostrate how to loop through a range. You can replace the range value for whatever range you need to loop through.

The first example loops through the entire column A range. The if statement tests the condition if the cell contains the text “FindMe” and shows a message box with the location of the text if found.

Public Sub LoopColumn()
Dim c As Range

For Each c In Range("A:A")

    If c.Value = "FindMe" Then
      MsgBox "FindMe found at " & c.Address
    End If

Next c

The next example loops through row 1 considering the entire row a range.

Public Sub LoopRow()
Dim c As Range

For Each c In Range("1:1")

    If c.Value = "FindMe" Then
      MsgBox "FindMe found at " & c.Address
    End If

Next c

End Sub
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Leave a comment | Trackback
  1. Kent
    October 21st, 2008 at 04:55
    Reply | Quote | #1

    dear sir,

    in the first example, you forget to put the End Sub at the end of the program.

    I learn something from this program. Thanks

  2. rishi doshi
    April 29th, 2009 at 11:04
    Reply | Quote | #2

    Hi,
    How to manually increment while using FOR EACH?
    e.g.
    for each cell in range(abc)
    if cell.value = “YES” then
    –>{increment cell = cell + 5}
    end if
    next

    we do it in normal FOR loops
    like.. if {something} then i = i+5

    I would like to achieve the same while using the FOR EACH loop
    thanks in advance!

    Rishi

  3. Bhanu
    October 19th, 2009 at 13:12
    Reply | Quote | #3

    Please give me the code to populate the recordset data into the excel file,where record set is having the data of 80,000 rows and having 500 columns.

    Please help me.

    Thanks

  4. rahul
    July 14th, 2010 at 20:55
    Reply | Quote | #4

    can anyone tell me what is wrong in this code. after inserting columns it is going back to insert statement and throwing “insert method range error”. Please giv any suggestions.

    Private Sub ComboBox1_Change()
    Sheet1.Unprotect

    Dim LastRowColA As Range
    Dim abc As Range
    If ComboBox1.Text = “Food” Then
    Worksheets(“Sheet3″).Activate
    Sheet1.Unprotect
    Set LastRowColA = Sheet2.Range(“J3″)

    Set abc = Sheet2.Range(“J2″).End(xlDown)

    Set Rng = Sheet2.Range(LastRowColA, abc)
    Sheet1.Activate
    For Each d In Rng.Cells
    ActiveCell = CStr(d.Value)
    Sheet1.Cells(17, 22).EntireColumn.Insert shift:=xlLeft

    Next d

    ElseIf ComboBox1.Text = “Drink” Then
    Worksheets(“Sheet3″).Activate
    Sheet1.Unprotect
    Set LastRowColA = Sheet2.Range(“M3″)

    Set abc = Sheet2.Range(“M2″).End(xlDown)

    Set Rng = Sheet2.Range(LastRowColA, abc)
    Sheet1.Activate
    For Each d In Rng.Cells
    ActiveCell = CStr(d.Value)

    Sheet1.Cells(17, 22).EntireColumn.Insert shift:=xlLeft

    Next d

    End If

    End Sub