How do range objects work?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken McLennan - 19 May 2006 10:56 GMT

[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

Reply to this Message

MattShoreson - 19 May 2006 11:51 GMT

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

Signature

MattShoreson

Reply to this Message

paul.robinson@it-tallaght.ie - 19 May 2006 12:09 GMT

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

Reply to this Message

Bob Phillips - 19 May 2006 12:51 GMT

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

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Hi
> .Cells, like .offset, is relative to the range but does not have to be
[quoted text clipped - 9 lines]
> regards
> Paul

Reply to this Message

Ken McLennan - 20 May 2006 10:39 GMT

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.

Reply to this Message