cells in a diagonal range

[Doug]

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

 

[Bob Umlas]

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]

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

 

[Trevor Shuttleworth]

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]

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

 

[Leo Heuser]

Hello Doug

Using the syntax Range("B2,c2,d2")
will create *three* distinct areas in the
range, in contrast to using
Range("B22") 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.