How do range objects work?

[Ken McLennan]

[This followup was posted to and a copy was sent to the cited author.]

G'day there One & All,

I'm back on the group with a question again, but this one's more
for curiosity than for need.

I'm sure that I've found a bug in Excel. It's more likely just a
bug in the way I perceive that Excel should work, but I'd rather blame
Microsoft than admit to a personal deficiency =)

What I have is a sheet with, amongst other things, a range object
consisting of 2 discontiguous rows of 13 cells each. Ie:

Set nGrid = Union(Sheet1.Range(Cells(ntrow, gsLft), Cells(ntrow,
gsLft + 12)), Sheet1.Range(Cells(nbrow, gsLft), Cells(nbrow, gsLft +
12)))

ntrow = top row
nbrow = bottom row
gsleft = left column

The variables give me the following address for nGrid -
$D$22:$P$22,$D$25:$P$25

That bit works fine. If I tell nGrid to have a green background
and glow in the dark then both sets of cells do exactly that.

I want to have the cells contain the numbers 1 to 26. Obviously,
13 in each row. I thought that I could use the ".item" property but I
couldn't figure out a way to do so. I then decided on to use the old
"For x = 1 to 26" technique thusly:

Dim x As Integer
For x = 1 To 26
nGrid.Cells(x).Value = x
Next

That worked fine for the first 13 cells, but then it all went
awry. The second row of 'x' values went into the correct columns but in
the row immediately underneath the first:

1 2 3 4 5 6 7 8 9 10 11 12 13
--------------------------------------
14 15 16 17 18 19 20 21 22 23 24 25 26

When it should have been:

1 2 3 4 5 6 7 8 9 10 11 12 13
--------------------------------------

--------------------------------------

--------------------------------------
14 15 16 17 18 19 20 21 22 23 24 25 26


A couple of debug.prints before & after for loop indicate that
Excel is well aware of the address, giving the same as detailed above.
However it doesn't write the values into row 25, but into 23!!! It's
obvious that the correct address is being read as indicated by the green
glowing in the dark. What gives? Why doesn't it write the values into
the correct row? The address is obviously being read ok as the columns
are all correct.

It's easy to get around, by just writing to the nominated row, but
I don't understand the mechanism behind why this doesn't work. Any ideas
anyone?

See ya
Ken McLennan
Qld, Australia

 

[MattShoreson]

this works fine for me....

Sub blah()
Dim x As Integer
ntrow = 22
nbrow = 25
gsLeft = 4

With Sheets("Sheet1")
Set ngrid = Application.Union(Range(.Cells(ntrow, gsLeft),
..Cells(ntrow, _
gsLeft + 12)), Range(.Cells(nbrow, gsLeft), .Cells(nbrow, gsLeft +
12)))
End With

x = 1

For Each xlcell In ngrid
xlcell.Value = x
x = x + 1
Next xlcell

End Sub

 

[paul.robinson]

Hi
..Cells, like .offset, is relative to the range but does not have to be
in the range. This is a feature not a bug(!).
To stay in the range you want something like
For each Cell in nGrid
i = i + i
Cell.Value = i
next Cell

This fills across the row before going to the next row.

regards
Paul

 

[Bob Phillips]

Also, when you set the range you should fully qualify it

With Sheet1
Set nGrid = Union(.Range(.Cells(ntRow, gsLft), .Cells(ntRow, gsLft +
12)), _
.Range(.Cells(nbRow, gsLft), .Cells(nbRow, gsLft + 12)))
End With



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Ken McLennan]

G'day there Matt, Paul, and Bob,

Thanks for taking the time to answer my inquiry. I think I
understand a bit better where I went wrong (although I'd still rather
blame MSoft <g> ).

I've now got my sheet working the way it's supposed to so that's
gotta be a plus.

Thanks once again for helping out. It really is appreciated.

See ya
Ken McLennan
Qld, Australia.