Iterate over irregular shaped range problem

[Mark Hanley]

I have set up a number of named ranges - each of which is an irregular
shape (as in not rectangular).

I have found that one can iterate through all cells in a rectangular
range with a loop like:

' Set all cells in named range to contain 'hello'
set ws = Sheets("aWorksheet")
For i = 1 To ws.Range("aRegularRange").Count
ws.Range("aRegularRange").Cells(i).Value = "hello"
Next i

BUT...

When I try to do the same with a non-rectangular range, it simply
moves down the first column in the range and keeps going downwards and
out of the range.

Is there a way round this? Is the Values property not supposed to be
used with just one argument? Is there a way of iterating through an
irregular range by specifying a single index value?

N.B - I can't use a 'For Each' as I want to jump through the cells in
regular intervals.

Thanks

Mark

 

[Jim Cone]

Non rectangular ranges are separated into rectangular ranges by Excel
when any operation is done on them.
These separate ranges are called "areas".
You have to loop thru each area and do your thing in order to
ensure that all cells are covered.
Check the help file for the "Areas" collection.
--
Jim Cone
Portland, Oregon USA



"Mark Hanley" <[email protected]>
wrote in message
I have set up a number of named ranges - each of which is an irregular
shape (as in not rectangular).

I have found that one can iterate through all cells in a rectangular
range with a loop like:

' Set all cells in named range to contain 'hello'
set ws = Sheets("aWorksheet")
For i = 1 To ws.Range("aRegularRange").Count
ws.Range("aRegularRange").Cells(i).Value = "hello"
Next i

BUT...

When I try to do the same with a non-rectangular range, it simply
moves down the first column in the range and keeps going downwards and
out of the range.

Is there a way round this? Is the Values property not supposed to be
used with just one argument? Is there a way of iterating through an
irregular range by specifying a single index value?

N.B - I can't use a 'For Each' as I want to jump through the cells in
regular intervals.

Thanks

Mark

 

[Alogon]

Hi Mark

Try to iterate each cell from row 1 of the irregular range; then do the same
with the row 2 and so on. Use something like this:

....
For Each rw In ws.Range("aRegularRange").rows
For Each cel In rw
....
Next
....
Next

Best regards from Brazil

Nathan

 

[Alogon]

Hi again Mark

I made a mistake in the last code.

Try this now:

For Each rw In ws.Range("aRegularRange").rows
For Each cel In rw.CELLS ' fixed code by adding CELLS
...
Next
Next

I tested here and it worked well.

Regards.

Nathan