[Excel 2010] For Each behavior that I don't understand

[David]

Excel 2010 - VBA

Consider the following example:
'**********************************
Public Sub test()

Dim currev As Variant

Range("A1:C4") = "a value"
Debug.Print Range("A1").CurrentRegion.Columns(1).Address

' Only one iteration!? Why?
For Each currev In Range("A1").CurrentRegion.Columns(1)
Debug.Print currev.Address
Next

' that's what I want to get
For Each currev In Range(Range("A1").CurrentRegion.Columns(1).Address)
Debug.Print currev.Address
Next

End Sub
'*****************************

Why in the first for loop I get only one iteration? I'm iterating over a
range like in the secondo loop.
Please somebody clarify.

TIA
David

 

[James Ravenswood]

The first loop is a loop over columns and there is only one. COnsider:

Public Sub test()

Dim currev As Variant

Range("A1:C4") = "a value"

For Each currev In Range("A1").CurrentRegion.Columns(1)
MsgBox currev.Address
Next

For Each currev In Range("A1").CurrentRegion.Columns(1).Cells
MsgBox currev.Address
Next

End Sub

 

[Don Guillett]

Excel 2010 - VBA



Consider the following example:

'**********************************

Public Sub test()



Dim currev As Variant



Range("A1:C4") = "a value"

Debug.Print Range("A1").CurrentRegion.Columns(1).Address



' Only one iteration!? Why?

For Each currev In Range("A1").CurrentRegion.Columns(1)

Debug.Print currev.Address

Next



' that's what I want to get

For Each currev In Range(Range("A1").CurrentRegion.Columns(1).Address)

Debug.Print currev.Address

Next



End Sub

'*****************************



Why in the first for loop I get only one iteration? I'm iterating over a

range like in the secondo loop.

Please somebody clarify.



TIA

David

For i = 1 To Range("A1").CurrentRegion.Columns(1).Rows.Count
MsgBox i
Next i

 

[David]

Il Sun, 19 Aug 2012 06:30:36 -0700 (PDT), Don Guillett ha scritto:

For i = 1 To Range("A1").CurrentRegion.Columns(1).Rows.Count
MsgBox i
Next i

Yes that's the warkaround I used, but I want to understand why my first loop
doesn't do what I expect.

David

 

[David]

Il Sun, 19 Aug 2012 05:33:48 -0700 (PDT), James Ravenswood ha scritto:

The first loop is a loop over columns and there is only one. COnsider:

Thank you! Now It's clear.

David