If I have a noncontiguous range oRange defined as "V31:W31,Y31:Z31" and
I iterate through the cells with "For Each cell In oRange.Cells" set the
cell value to 5, everything works fine. However, if I iterate through with:
For i = 1 To 4
oRange.Cells(i).Value = 5
next i

The values go into "V31:W32"!
Why should using the index not work the same as a "for each" statement?
How do I reference the 3rd cell in the noncontiguous range without
iterating through the first 2?
I assume this is a bug...

observed in Office 2003

Matthew Wieder wrote:

If I have a noncontiguous range oRange defined as "V31:W31,Y31:Z31" and
I iterate through the cells with "For Each cell In oRange.Cells" set the
cell value to 5, everything works fine. However, if I iterate through
with:
For i = 1 To 4
oRange.Cells(i).Value = 5
next i

The values go into "V31:W32"!
Why should using the index not work the same as a "for each" statement?
How do I reference the 3rd cell in the noncontiguous range without
iterating through the first 2?
I assume this is a bug...

Try this small test

Sub test()
Dim rng As Range
Set rng = Range("V31:W31,Y31:Z31")
MsgBox rng.Areas(2).Cells(1).Value
End Sub

You can use this without a loop to make the value 5
Range("a1:b1,d1:E1").Value = 5

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Matthew Wieder" wrote in message ...
If I have a noncontiguous range oRange defined as "V31:W31,Y31:Z31" and
I iterate through the cells with "For Each cell In oRange.Cells" set the
cell value to 5, everything works fine. However, if I iterate through with:
For i = 1 To 4
oRange.Cells(i).Value = 5
next i

The values go into "V31:W32"!
Why should using the index not work the same as a "for each" statement?
How do I reference the 3rd cell in the noncontiguous range without
iterating through the first 2?
I assume this is a bug...

Certainly that works, but the idea behind using the Cells collection is
not to have to know or worry about how many cells are in each Area to
get to the 4th cell. As you have pointed out, if I can hard code it, I
don't have an issue.

Ron de Bruin wrote:

Try this small test

Sub test()
Dim rng As Range
Set rng = Range("V31:W31,Y31:Z31")
MsgBox rng.Areas(2).Cells(1).Value
End Sub

You can use this without a loop to make the value 5
Range("a1:b1,d1:E1").Value = 5

Hi Matthew,

Thanks for posting in the community!

Currentlly we are performing some research on this problem, and we will reply you ASAP if we get any results!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Matthew

AFAIK it is not possible with a Non Contiguous Range

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Matthew Wieder" wrote in message ...
Certainly that works, but the idea behind using the Cells collection is
not to have to know or worry about how many cells are in each Area to
get to the 4th cell. As you have pointed out, if I can hard code it, I
don't have an issue.

Ron de Bruin wrote:

Try this small test

Sub test()
Dim rng As Range
Set rng = Range("V31:W31,Y31:Z31")
MsgBox rng.Areas(2).Cells(1).Value
End Sub

You can use this without a loop to make the value 5
Range("a1:b1,d1:E1").Value = 5