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

D

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
 
J

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
 
D

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
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top