Hello this is Robert.

I need some help how to loop both by row and by column in the selected
area.

When i select a certain variable range, let's call it My_Range
( yesterday My_Range = b7:b7, today My_Range = c9:f27 tomorrow
whatever )

I want to run i macro, to test for a particular condition each single
cell in My_range.

How do i loop from the first to the last cell, changing row and or
column.



Many Thanks.

Best Regards.

Robert.

Robert,

To loop down each column, you can use a For Each loop. E.g.,

Dim Rng As Range
For Each Rng In Range("MyRange")
Debug.Print Rng.Address
Next Rng


To loop across rows, use two For Each loops. E.g.,

Dim Rng As Range
Dim Rw As Range
For Each Rw In Range("MyRange").Rows
For Each Rng In Rw.Cells
Debug.Print Rng.Address
Next Rng
Next Rw


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


"Bubu" wrote in message
om...
Hello this is Robert.

I need some help how to loop both by row and by column in the selected
area.

When i select a certain variable range, let's call it My_Range
( yesterday My_Range = b7:b7, today My_Range = c9:f27 tomorrow
whatever )

I want to run i macro, to test for a particular condition each single
cell in My_range.

How do i loop from the first to the last cell, changing row and or
column.



Many Thanks.

Best Regards.

Robert.

if my_range is a variable reference to a range

Dim my_Range as Range
Dim cell as Range
set my_Range = Range("C9:F27")
for each cell in my_Range
msgbox cell.Address
Next

if my_Range is a defined name

dim rng as Range
dim cell as Range
Range("C9:F27").Name = "my_Range"
set rng = Range("my_Range")
for each cell in rng
msgbox cell.address
Next

--
Regards,
Tom Ogilvy


Bubu wrote in message
om...
Hello this is Robert.

I need some help how to loop both by row and by column in the selected
area.

When i select a certain variable range, let's call it My_Range
( yesterday My_Range = b7:b7, today My_Range = c9:f27 tomorrow
whatever )

I want to run i macro, to test for a particular condition each single
cell in My_range.

How do i loop from the first to the last cell, changing row and or
column.



Many Thanks.

Best Regards.

Robert.

Robert,

If you want to process each cell in your range you can use:

Dim oCell As Range

For Each oCell In My_Range
MsgBox oCell.Address
Next oCell
End Sub

If you want more control over columns and rows you can use:

Dim i As Long
Dim j As Long

With My_Range

For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
MsgBox .Cells(i, j).Address
Next j
Next i

End With

--

John Green - Excel MVP
Sydney
Australia


"Bubu" wrote in message om...
Hello this is Robert.

I need some help how to loop both by row and by column in the selected
area.

When i select a certain variable range, let's call it My_Range
( yesterday My_Range = b7:b7, today My_Range = c9:f27 tomorrow
whatever )

I want to run i macro, to test for a particular condition each single
cell in My_range.

How do i loop from the first to the last cell, changing row and or
column.



Many Thanks.

Best Regards.

Robert.

Loop through each cell in My_Range:

dim rCell as range

for each rCell in My_Range
'Your code here
debug.print rCell.addresslocal
next rCell


Loop through each cell in My_Range, traverse by colums:

Dim rCell as range
Dim rCol as range

for each rCol in My_Range.columns
for each rCell in rCol.Cells
'Your code here
debug.print rCell.addresslocal
next rCell
next rCol


Loop through each cell in My_Range, traverse by rows:

Dim rCell as range
Dim rRow as range

for each rRow in My_Range.Rows
For each rCell in rRow.cells
'Your code here
debug.print rCell.addresslocal
next rCell
next rRow


Bill Barclift


"Bubu" wrote in message
om...
Hello this is Robert.

I need some help how to loop both by row and by column in the selected
area.

When i select a certain variable range, let's call it My_Range
( yesterday My_Range = b7:b7, today My_Range = c9:f27 tomorrow
whatever )

I want to run i macro, to test for a particular condition each single
cell in My_range.

How do i loop from the first to the last cell, changing row and or
column.



Many Thanks.

Best Regards.

Robert.

Add this to this code and it will do the work on the current
selection.

Add it after the DIM line

Dim My_Range as Range

Set My_Range = Selection


Chrissy.



John Green wrote
Robert,

If you want to process each cell in your range you can use:

Dim oCell As Range

For Each oCell In My_Range
MsgBox oCell.Address
Next oCell
End Sub

If you want more control over columns and rows you can use:

Dim i As Long
Dim j As Long

With My_Range

For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
MsgBox .Cells(i, j).Address
Next j
Next i

End With

--

John Green - Excel MVP
Sydney
Australia


"Bubu" wrote in message om...
Hello this is Robert.

I need some help how to loop both by row and by column in the selected
area.

When i select a certain variable range, let's call it My_Range
( yesterday My_Range = b7:b7, today My_Range = c9:f27 tomorrow
whatever )

I want to run i macro, to test for a particular condition each single
cell in My_range.

How do i loop from the first to the last cell, changing row and or
column.



Many Thanks.

Best Regards.

Robert.

You all saved me a lot of time.

Thank You for Your help.

Best Regerds.
Robert.