Is 'For Each' and 'For i = 1 to n' work in different ways?

[prabha_friend]

Data:
C1: 3
B2: 2
D2: 2
A3: 1
E3: 1
B4: 2
D4: 2
c3: 3

Sub mac()
'MsgBox Selection.Cells.Count
For Each cell In Selection.Cells
    MsgBox cell.Address
Next cell
End Sub

Sub mac1()
'MsgBox Selection.Cells.Count
For i = 1 To Selection.Cells.Count
    MsgBox Selection.Cells(i).Address
Next i
End Sub

 

[ranman256]

for I = , is for numeric variables

for EACH is usu for objects... text boxes, etc

 

[prabha_friend]

Please simulate the data and run both of the procedures. Kindly do it.

 

[mikerickson]

It depends on the use.

If you are going to the same thing to each cell (e.g. add 1 to it) then For Each is a good choice.
If what you are going to do varies with which cell (e.g. add 1 to the first, 2 to the second,...) the For I = ... is good.

If you are trying to keep two arrays (or ranges) in sync, then For I might be better.

Which to use depends on what you are going to do.

 

[prabha_friend]

Sub mac()
'MsgBox Selection.Cells.Count
For Each cell In Selection.Cells
    MsgBox cell.Address
Next cell
End Sub

Gives me the proper result:
C1
B2
D2
A3
E3
B4
D4
c3

but...

Sub mac1()
'MsgBox Selection.Cells.Count
For i = 1 To Selection.Cells.Count
    MsgBox Selection.Cells(i).Address
Next i
End Sub

gives...

C1
C2
C3
C4
C5
C6
C7
C8

why?

so 'for each' and 'for i = 1 to n' is entirely different when comes to selection? I want to store them in a 2D array as per row,col...

 

[mikerickson]

The Cells property properly needs two arguments, and the result is not limited to the range it is a member of.
For what you are looking for, the Item property would work best.

MsgBox Selection.Item(I).Address