CODEWITHSUNDEEP
vbaexcelforeachiteration
GrahamD
GrahamD

Reputation: 61

Identifying the iteration of a For Each loop in VBA?

If I have a loop that commences:

For each c in Range("A1:C8")

Is there a property of the placeholder c (c.count, c.value, c.something,...) that identifies the number of times the loop has iterated thus far? I would rather use something like this than including another variable.

Upvotes: 6

Views: 23641

Answers (3)

K. Rhodes
K. Rhodes

Reputation: 1

(Revised)

Using Column or Row properties, as appropriate to the direction you are iterating, you can compute an ordinal number on the fly. Thus

For Each c1 in myRange
    myOrdinal = c1.row - myRange.row + 1       ' down contiguous cells in one column
    myOrdinal = c1.Column - myRange.Column + 1 ' contiguous columns, L2R
Next

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

Instead of using a "for each c in range" you can do something like this:

Dim c as Long 'presumably you did this as a Range, just change it to Long.
Dim myRange as Range 'Use a range variable which will be easier to call on later
Set myRange = Range("A1:C8")

For c = 1 to myRange.Cells.Count
    'Do something to the cell, identify it as myRange.Cells(c), for example:
    myRange.Cells(c).Font.Bold = True   '<--- replace with your code that affects the cell
Next

This allows you to do the exact same For/Next loop, without including an unnecessary counter variable. In this case, c is a counter but also serves the purpose of identifying the cell being impacted by the code.

Upvotes: 4

Dale M
Dale M

Reputation: 2473

You need to count it yourself like this

Dim i as integer
i = 0    
For each c in Range("A1:C8")
    i = i + 1

Or

Dim i as integer
Dim c as Range
For i = 0 to Range("A1:C8").Count - 1
    Set c = Range("A1:C8").Cells(i)

Upvotes: 1

Related Questions