Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for contiguous cells
For a game, I need to determine whether non-blank cells in range
A1:J10 are orthogonally contiguous. If I had values in cells A1, A2, A3, B3 and C3 then the result would be true. However, remove the value from cell A3 and the result is now false. Any ideas how to do this test? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for contiguous cells
if intersect(Range("A1").End(xldown).End(xltoRight),R ange("A1:J10")) is
nothing then if intersect(Range("A1").End(xltoRight).End(xldown),R ange("A1:J10)) is nothing then msgbox "Not orthogonally contiguous" end if End if of if isemtpy(Range("A1").End(xldown).offset(0,1)) then if isempty(Range("A1").End(xltoRight).Offset(1,0)) Then msgbox "Not orthogonally contiguous" end if End if -- Regards, Tom Ogilvy "jamieuk" wrote in message om... For a game, I need to determine whether non-blank cells in range A1:J10 are orthogonally contiguous. If I had values in cells A1, A2, A3, B3 and C3 then the result would be true. However, remove the value from cell A3 and the result is now false. Any ideas how to do this test? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for contiguous cells
Thanks Tom, I appreciate you taking the time to reply. Unfortunately,
I couldn't get either of your suggestions to work. I took your second suggestion, because it takes account of range A1:J10 (the 'board'). I need to test that every non-blank cell is contiguous with all others (not just cell A1), so I put the code in a loop: Sub Test() Dim oBoard As Excel.Range Dim oSqaure As Excel.Range Dim blnContiguous As Boolean Set oBoard = Range("A1:J10") blnContiguous = True For Each oSqaure In oBoard If Len(oSqaure.Value) 0 Then blnContiguous = IsContiguous(oSqaure, oBoard) If Not blnContiguous Then Exit For End If End If Next If Not blnContiguous Then MsgBox "Not orthogonally contiguous" End If End Sub Function IsContiguous(ByVal Square As Excel.Range, _ ByVal Board As Excel.Range) As Boolean IsContiguous = True If Intersect(Square.End(xlDown).End(xlToRight), Board) Is Nothing Then If Intersect(Square.End(xlToRight).End(xlDown), Board) Is Nothing Then IsContiguous = False End If End If End Function When there are non-blank values in A1 and A2 only (obviously contiguous), both combinations of xlDown and xlRight (for both cells) end up off the board and IsContiguous incorrectly returns false. An approach seems to be emerging i.e. count the cell immediately to the left or below the current cell but also take account of whether the current cell has already been counted. But it's making my head hurt! Any further suggestions would be most welcome. -- "Tom Ogilvy" wrote in message ... if intersect(Range("A1").End(xldown).End(xltoRight),R ange("A1:J10")) is nothing then if intersect(Range("A1").End(xltoRight).End(xldown),R ange("A1:J10)) is nothing then msgbox "Not orthogonally contiguous" end if End if of if isemtpy(Range("A1").End(xldown).offset(0,1)) then if isempty(Range("A1").End(xltoRight).Offset(1,0)) Then msgbox "Not orthogonally contiguous" end if End if -- Regards, Tom Ogilvy "jamieuk" wrote in message om... For a game, I need to determine whether non-blank cells in range A1:J10 are orthogonally contiguous. If I had values in cells A1, A2, A3, B3 and C3 then the result would be true. However, remove the value from cell A3 and the result is now false. Any ideas how to do this test? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non-contiguous cells | Excel Discussion (Misc queries) | |||
Testing Cells | New Users to Excel | |||
TESTING A RANGE OF CELLS | New Users to Excel | |||
TESTING A RANGE OF CELLS | Excel Worksheet Functions | |||
testing for non-empty cells | Excel Programming |