Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over irregular shaped range problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over irregular shaped range problem
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over irregular shaped range problem
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 "Jim Cone" wrote: 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterate over irregular shaped range problem
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 "Alogon" wrote: 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 "Jim Cone" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterate over a Range of data - create arrays or ranges | Excel Programming | |||
Pulling "Irregular Shaped" Data from Excel | Excel Programming | |||
How To Iterate a range of cells? | Excel Programming | |||
Iterate through the values in a named range? | Excel Programming |