Navigate through selected range to store values...
 

Need some ideas on how to do this correctly as I'm failing to figure it out.
I have selected a range of cells in a column (using VBA) and want to store
the value in each to a string array.

Set r = Range("Tardy5") 'Tarydy5 is name for range F6:F36
r.Select
n = r.Rows.Count
ReDim arrTardy5Cells(n)

'Loop through selected cells.
'Set curCell = r.Cells(1, i) moved out of the loop below to test.
For i = 1 To n
arrTardy5Cells(i) = ActiveCell.Value 'store value to array
ActiveCell(i, 0).Activate 'expect active cell to advance
down one cell
Next i

The above loop does not behave as I expected it to, so need to know what I'm
missing. Thanks!

Navigate through selected range to store values...
 

You can just do:

Dim arrTardy5Cells

arrTardy5Cells = Range("Tardy5")

This will be a variant, 1-D, 1-based array, but that is probably no problem.
You can always go trough this array with a double loop, for example:

for r = 1 to ubound(arrTardy5Cells)
for c = 1 to ubound(arrTardy5Cells,2)
msgbox arrTardy5Cells(r,c)
next
next

In any case there is no need to activate cells.

RBS




"Richth" wrote in message
...
Need some ideas on how to do this correctly as I'm failing to figure it
out.
I have selected a range of cells in a column (using VBA) and want to store
the value in each to a string array.

Set r = Range("Tardy5") 'Tarydy5 is name for range F6:F36
r.Select
n = r.Rows.Count
ReDim arrTardy5Cells(n)

'Loop through selected cells.
'Set curCell = r.Cells(1, i) moved out of the loop below to test.
For i = 1 To n
arrTardy5Cells(i) = ActiveCell.Value 'store value to
array
ActiveCell(i, 0).Activate 'expect active cell to
advance
down one cell
Next i

The above loop does not behave as I expected it to, so need to know what
I'm
missing. Thanks!

Navigate through selected range to store values...
 

Thanks RB,

Thats way easier. However, there must be an easier way to do what I was
going to do next, so perhaps I had better say what I am fully trying to do.
<g

I want to copy each row that has a date value in the Tardy5 range and place
it below row 40, being sure not to overwrite one of the rows below row 40.

Thanks! I appreciate the help.

"RB Smissaert" wrote:

You can just do:

Dim arrTardy5Cells

arrTardy5Cells = Range("Tardy5")

This will be a variant, 1-D, 1-based array, but that is probably no problem.
You can always go trough this array with a double loop, for example:

for r = 1 to ubound(arrTardy5Cells)
for c = 1 to ubound(arrTardy5Cells,2)
msgbox arrTardy5Cells(r,c)
next
next

In any case there is no need to activate cells.

RBS




"Richth" wrote in message
...
Need some ideas on how to do this correctly as I'm failing to figure it
out.
I have selected a range of cells in a column (using VBA) and want to store
the value in each to a string array.

Set r = Range("Tardy5") 'Tarydy5 is name for range F6:F36
r.Select
n = r.Rows.Count
ReDim arrTardy5Cells(n)

'Loop through selected cells.
'Set curCell = r.Cells(1, i) moved out of the loop below to test.
For i = 1 To n
arrTardy5Cells(i) = ActiveCell.Value 'store value to
array
ActiveCell(i, 0).Activate 'expect active cell to
advance
down one cell
Next i

The above loop does not behave as I expected it to, so need to know what
I'm
missing. Thanks!

Navigate through selected range to store values...
 

Actually figured this out easily enough using the recorder. Appreciate the
help. This method works great.

"RB Smissaert" wrote:

You can just do:

Dim arrTardy5Cells

arrTardy5Cells = Range("Tardy5")

This will be a variant, 1-D, 1-based array, but that is probably no problem.
You can always go trough this array with a double loop, for example:

for r = 1 to ubound(arrTardy5Cells)
for c = 1 to ubound(arrTardy5Cells,2)
msgbox arrTardy5Cells(r,c)
next
next

In any case there is no need to activate cells.

RBS




"Richth" wrote in message
...
Need some ideas on how to do this correctly as I'm failing to figure it
out.
I have selected a range of cells in a column (using VBA) and want to store
the value in each to a string array.

Set r = Range("Tardy5") 'Tarydy5 is name for range F6:F36
r.Select
n = r.Rows.Count
ReDim arrTardy5Cells(n)

'Loop through selected cells.
'Set curCell = r.Cells(1, i) moved out of the loop below to test.
For i = 1 To n
arrTardy5Cells(i) = ActiveCell.Value 'store value to
array
ActiveCell(i, 0).Activate 'expect active cell to
advance
down one cell
Next i

The above loop does not behave as I expected it to, so need to know what
I'm
missing. Thanks!