Walking Through Cells Using a Loop

[Negnog]

BEGINNER'S QUESTION:
I am creating an application in Excel that will use a block of cells for
display - 9 rows and 9 columns - A1-A9, B1-B9, ..., I1-I9. In several
places, I would like to look at or write to each cell using a FOR loop in VBA:

FOR row=1 to 9
FOR col=1 TO 9
write to cell at (row, col)
NEXT col
NEXT row

How do I create cell references dynamically in a loop based on "row" and
"col"? Is there a better way to programatically cycle through a series of
cells other than using FOR loops?

Thank you.

[Dennis W. Bulgrien]

Try ActiveWindow.ActiveSheet.Cells(row, col) or just Cells(row,col).

"Negnog" <Negnog@discussions.microsoft.com> wrote in message
news:D55C568A-FDA9-44C0-B6EE-63930970DECD@microsoft.com...

How do I create cell references dynamically in a loop based on "row" and
"col"? Is there a better way to programatically cycle through a series of
cells other than using FOR loops?

Thank you.

[Chip Pearson]

Use

Cells(row,col).Value = 123

As an aside, 'row' is a reserved word in Excel/VBA, and good
programming practice would preclude it from being used as a
variable name. Use 'RowNum' or something similar instead.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Negnog" <Negnog@discussions.microsoft.com> wrote in message
news:D55C568A-FDA9-44C0-B6EE-63930970DECD@microsoft.com...
> BEGINNER'S QUESTION:
> I am creating an application in Excel that will use a block of
> cells for
> display - 9 rows and 9 columns - A1-A9, B1-B9, ..., I1-I9. In
> several
> places, I would like to look at or write to each cell using a
> FOR loop in VBA:
>
> FOR row=1 to 9
> FOR col=1 TO 9
> write to cell at (row, col)
> NEXT col
> NEXT row
>
> How do I create cell references dynamically in a loop based on
> "row" and
> "col"? Is there a better way to programatically cycle through
> a series of
> cells other than using FOR loops?
>
> Thank you.
>

[bigwheel]

Cells(Row, col) = value to write to cell

"Negnog" wrote:

> BEGINNER'S QUESTION:
> I am creating an application in Excel that will use a block of cells for
> display - 9 rows and 9 columns - A1-A9, B1-B9, ..., I1-I9. In several
> places, I would like to look at or write to each cell using a FOR loop in VBA:
>
> FOR row=1 to 9
> FOR col=1 TO 9
> write to cell at (row, col)
> NEXT col
> NEXT row
>
> How do I create cell references dynamically in a loop based on "row" and
> "col"? Is there a better way to programatically cycle through a series of
> cells other than using FOR loops?
>
> Thank you.
>

[Dennis W. Bulgrien]

For loops are good. You might look at For Each of a Range object, example:
Dim c
For Each c In ActiveWindow.RangeSelection
Next c

"Negnog" <Negnog@discussions.microsoft.com> wrote in message
news:D55C568A-FDA9-44C0-B6EE-63930970DECD@microsoft.com...

"col"? Is there a better way to programatically cycle through a series of
cells other than using FOR loops?