How do I get For Each C in Range to read columns first?

  • Thread starter Thread starter pedi
  • Start date Start date
P

pedi

Ok, here is my problem:
I have a range (say A1:B3) and I am using something like this:

For Each c In Range(A1:B3)
I=I+1
Cells(5,I)=c.value
Next c

If my range looks like :
A B
--------
11 100
22 200
33 300

the output column "E" would be:
11
100
22
200
33
300

but I want the "for each loop" to scan the columns first so the output
would be :
11
22
33
100
200
300

I would appreciate any hint.
Thanks
 
Untested air code:

Set rng = Range(A1:B3)
For c = 1 to rng.Columns.Count
For r = 1 to rng.Rows.Count
i=i+1
Cells(5, i)=rng.Cells(r,c).Value
Next r
Next c

HTH,
 
pedi wrote...
Ok, here is my problem:
I have a range (say A1:B3) and I am using something like this:

For Each c In Range(A1:B3)
I=I+1
Cells(5,I)=c.value
Next c

If my range looks like :
A B
--------
11 100
22 200
33 300

the output column "E" would be:
11
100
22
200
33
300

but I want the "for each loop" to scan the columns first so the output
would be :
11
22
33
100
200
300
Click to expand...

Why screw around with macros to do this? Formulas would be sufficient.
If the topmost result would be in cell E1, use formulas like

=INDEX($A$1:$B$3,MOD(ROWS(E$1:E1)-1,3)+1,INT((ROWS(E$1:E1)+2)/3))

and fill down into E2:E6.

If your example was overly simplified and you need to use VBA for
something more complicated, if you need a specific iteration order,
make it explicit in your code.


Dim r As Range, c As Range

For Each c In rng.Columns
For Each r In c.Cells
'whatever
Next r
Next c


Alternatively, make considered use of Excel/VBA's lack of
orthogonality. While 'For Each x In y' iterates by column then by row
througn range objects, it iterates by first dimension, then second,
then third, etc. through arrays, so for 2D arrays, by row then by
column.

Given your data in A1:B3 in the active worksheet, compare columns E and
F generated by the following macro.


Sub foo()
Dim v As Variant, x As Variant, n As Long
Set v = Range("A1:B3").Cells
n = 1
For Each x In v
Cells(n, 5).Value = x.Value
n = n + 1
Next x
v = Range("A1:B3").Value
n = 1
For Each x In v
Cells(n, 6).Value = x
n = n + 1
Next x
End Sub


Annoying, ain't it?!
 
Back
Top