NonContiguous Range Iteration Bug?

[Matthew Wieder]

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...

 

[Matthew Wieder]

observed in Office 2003

 

[Ron de Bruin]

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

 

[Matthew Wieder]

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.

 

[Wei-Dong Xu [MSFT]]

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.

 

[Ron de Bruin]

Hi Matthew

AFAIK it is not possible with a Non Contiguous Range