cells in a diagonal range
 

Hello,

I've been goofing around, writing a tic-tac-toe game. I define the eight
possible winning rows as ranges. I can refer to the Cells in the horizontal
and vertical ranges by saying something like:

for i = 1 to 3
rows(1).Cells(i).select
next i

However, for the two diagonal rows, this select statement selects the
topmost cell in the range and the two cells directly below the first cell,
not the other two actual cells in the range. Is there a simple syntax that
will work for all the ranges? Like I say, just goofing around, but it irks
me to treat these two rows differently.

Thank you,

Doug

cells in a diagonal range
 

You can define the 8 ranges with names, like "rg1","rg2",... where Rg7 is
maybe =A1,B2,C3 and Rg8 is A3,B2,C1, then you can do your loop as
For i= 1 to 8
range("Rg" & i).Select
Next

Bob Umlas
'Excel MVP
"Doug" wrote in message
...
Hello,

I've been goofing around, writing a tic-tac-toe game. I define the eight
possible winning rows as ranges. I can refer to the Cells in the
horizontal
and vertical ranges by saying something like:

for i = 1 to 3
rows(1).Cells(i).select
next i

However, for the two diagonal rows, this select statement selects the
topmost cell in the range and the two cells directly below the first cell,
not the other two actual cells in the range. Is there a simple syntax
that
will work for all the ranges? Like I say, just goofing around, but it
irks
me to treat these two rows differently.

Thank you,

Doug

cells in a diagonal range
 

Bob,

I wasn't clear. I'm trying to refer to the cells within the ranges. In
your example below, the statement "Rg7.Cells(2).Select" will select A2 not
B2. I hope that makes sense.

Oh yeah, I'm using XL2000 in Win XP.

Thanks,

Doug



"Bob Umlas" wrote in message
...
You can define the 8 ranges with names, like "rg1","rg2",... where Rg7 is
maybe =A1,B2,C3 and Rg8 is A3,B2,C1, then you can do your loop as
For i= 1 to 8
range("Rg" & i).Select
Next

Bob Umlas
'Excel MVP
"Doug" wrote in message
...
Hello,

I've been goofing around, writing a tic-tac-toe game. I define the
eight
possible winning rows as ranges. I can refer to the Cells in the
horizontal
and vertical ranges by saying something like:

for i = 1 to 3
rows(1).Cells(i).select
next i

However, for the two diagonal rows, this select statement selects the
topmost cell in the range and the two cells directly below the first
cell,
not the other two actual cells in the range. Is there a simple syntax
that
will work for all the ranges? Like I say, just goofing around, but it
irks
me to treat these two rows differently.

Thank you,

Doug

cells in a diagonal range
 

Doug

I don't think you can do what you want because you don't have a contiguous
range. I think you'll need to work with areas. Some examples from the
immediate window:

?range("rng3").Address
$B$4:$D$4
?range("rng7").Address
$B$2,$C$3,$D$4
?range("rng8").Address
$D$2,$C$3,$B$4
for each cell in range("rng8"): ?cell.Address : next
$D$2
$C$3
$B$4
?range("rng8").areas.Count
3
?range("rng8").areas(1).Address
$D$2
?range("rng8").areas(2).Address
$C$3
?range("rng8").areas(3).Address
$B$4

Regards

Trevor


"Doug" wrote in message
...
Bob,

I wasn't clear. I'm trying to refer to the cells within the ranges. In
your example below, the statement "Rg7.Cells(2).Select" will select A2 not
B2. I hope that makes sense.

Oh yeah, I'm using XL2000 in Win XP.

Thanks,

Doug



"Bob Umlas" wrote in message
...
You can define the 8 ranges with names, like "rg1","rg2",... where Rg7
is
maybe =A1,B2,C3 and Rg8 is A3,B2,C1, then you can do your loop as
For i= 1 to 8
range("Rg" & i).Select
Next

Bob Umlas
'Excel MVP
"Doug" wrote in message
...
Hello,

I've been goofing around, writing a tic-tac-toe game. I define the
eight
possible winning rows as ranges. I can refer to the Cells in the
horizontal
and vertical ranges by saying something like:

for i = 1 to 3
rows(1).Cells(i).select
next i

However, for the two diagonal rows, this select statement selects the
topmost cell in the range and the two cells directly below the first
cell,
not the other two actual cells in the range. Is there a simple syntax
that
will work for all the ranges? Like I say, just goofing around, but it
irks
me to treat these two rows differently.

Thank you,

Doug

cells in a diagonal range
 

Trevor,

At one point I was using For Next when I got a "type mismatch" error which I
didn't understand and avoided by going to this approach. I hadn't thought
of areas, though.

Thanks for the elucidation,

Doug

"Trevor Shuttleworth" wrote in message
...
Doug

I don't think you can do what you want because you don't have a contiguous
range. I think you'll need to work with areas. Some examples from the
immediate window:

?range("rng3").Address
$B$4:$D$4
?range("rng7").Address
$B$2,$C$3,$D$4
?range("rng8").Address
$D$2,$C$3,$B$4
for each cell in range("rng8"): ?cell.Address : next
$D$2
$C$3
$B$4
?range("rng8").areas.Count
3
?range("rng8").areas(1).Address
$D$2
?range("rng8").areas(2).Address
$C$3
?range("rng8").areas(3).Address
$B$4

Regards

Trevor


"Doug" wrote in message
...
Bob,

I wasn't clear. I'm trying to refer to the cells within the ranges. In
your example below, the statement "Rg7.Cells(2).Select" will select A2
not
B2. I hope that makes sense.

Oh yeah, I'm using XL2000 in Win XP.

Thanks,

Doug



"Bob Umlas" wrote in message
...
You can define the 8 ranges with names, like "rg1","rg2",... where Rg7
is
maybe =A1,B2,C3 and Rg8 is A3,B2,C1, then you can do your loop as
For i= 1 to 8
range("Rg" & i).Select
Next

Bob Umlas
'Excel MVP
"Doug" wrote in message
...
Hello,

I've been goofing around, writing a tic-tac-toe game. I define the
eight
possible winning rows as ranges. I can refer to the Cells in the
horizontal
and vertical ranges by saying something like:

for i = 1 to 3
rows(1).Cells(i).select
next i

However, for the two diagonal rows, this select statement selects
the
topmost cell in the range and the two cells directly below the first
cell,
not the other two actual cells in the range. Is there a simple
syntax
that
will work for all the ranges? Like I say, just goofing around, but
it
irks
me to treat these two rows differently.

Thank you,

Doug

cells in a diagonal range
 

Hello Doug

Using the syntax Range("B2,c2,d2")
will create *three* distinct areas in the
range, in contrast to using
Range("B2:D2") and
Union(Range("b2"), Range("C2"), Range("D2")),
which will both create *one* area, so for a uniform
way of getting the first cell of any of the eight ranges
you can use Areas(1), the middle cell Areas(2) and
the third cell Areas(3), see below.
You may find this syntax useful in your investigation
of TicTacToe :-)


Sub TicTacToe()
'Leo Heuser, 5 Nov. 2003
Dim Counter As Long
Dim Rng(1 To 8) As Range

Set Rng(1) = Range("B2,c2,d2")
Set Rng(2) = Range("B3,c3,d3")
Set Rng(3) = Range("B4,c4,d4")
Set Rng(4) = Range("B2,b3,b4")
Set Rng(5) = Range("c2,c3,c4")
Set Rng(6) = Range("d2,d3,d4")
Set Rng(7) = Range("B2,c3,d4")
Set Rng(8) = Range("B4,c3,d2")

For Counter = 1 To 8
MsgBox Rng(Counter).Areas(1).Address
MsgBox Rng(Counter).Areas(2).Address
MsgBox Rng(Counter).Areas(3).Address
Next Counter

End Sub

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.




"Doug" skrev i en meddelelse
...
Trevor,

At one point I was using For Next when I got a "type mismatch" error which
I
didn't understand and avoided by going to this approach. I hadn't thought
of areas, though.

Thanks for the elucidation,

Doug

"Trevor Shuttleworth" wrote in message
...
Doug

I don't think you can do what you want because you don't have a
contiguous
range. I think you'll need to work with areas. Some examples from the
immediate window:

?range("rng3").Address
$B$4:$D$4
?range("rng7").Address
$B$2,$C$3,$D$4
?range("rng8").Address
$D$2,$C$3,$B$4
for each cell in range("rng8"): ?cell.Address : next
$D$2
$C$3
$B$4
?range("rng8").areas.Count
3
?range("rng8").areas(1).Address
$D$2
?range("rng8").areas(2).Address
$C$3
?range("rng8").areas(3).Address
$B$4

Regards

Trevor


"Doug" wrote in message
...
Bob,

I wasn't clear. I'm trying to refer to the cells within the ranges.
In
your example below, the statement "Rg7.Cells(2).Select" will select A2
not
B2. I hope that makes sense.

Oh yeah, I'm using XL2000 in Win XP.

Thanks,

Doug



"Bob Umlas" wrote in message
...
You can define the 8 ranges with names, like "rg1","rg2",... where
Rg7
is
maybe =A1,B2,C3 and Rg8 is A3,B2,C1, then you can do your loop as
For i= 1 to 8
range("Rg" & i).Select
Next

Bob Umlas
'Excel MVP
"Doug" wrote in message
...
Hello,

I've been goofing around, writing a tic-tac-toe game. I define
the
eight
possible winning rows as ranges. I can refer to the Cells in the
horizontal
and vertical ranges by saying something like:

for i = 1 to 3
rows(1).Cells(i).select
next i

However, for the two diagonal rows, this select statement selects
the
topmost cell in the range and the two cells directly below the
first
cell,
not the other two actual cells in the range. Is there a simple
syntax
that
will work for all the ranges? Like I say, just goofing around,
but
it
irks
me to treat these two rows differently.

Thank you,

Doug