Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Qadriyah Malik   on Dec 16 In MS Office Category.

  
Question Answered By: Vickie Smith   on Dec 16

It occurred to me that if you are testing two sets at a time from a total of
25 sets, that's only about 300 tests at most. You don't need a complicated
recursive routine for that, just a couple of nested iterative loops. So, I
rewrote the code I sent you yesterday as follows:

Option Explicit

'Global variables for WarHawk's problem
Public Type SetInfo
SetNbr As Integer 'Set number  (assigned sequentially by macro)
Val1 As Integer 'First value in set
Val2 As Integer 'Second value in set
Val3 As Integer 'Third value in set
Addr As String 'Address of cell
End Type
Public Cellz() As SetInfo, SetCnt As Integer

Sub FindSoln()
Dim x As Integer, aa As Integer, bb As Integer
'Assuming data starts in A1 on the active sheet. Sets values  are concatenated
into 3-digit numbers.
ActiveSheet.Range("A1").Activate
x% = 1
SetCnt% = -1
Do While Len(ActiveCell.Value) > 0
SetCnt% = SetCnt% + 1
ReDim Preserve Cellz(SetCnt%)
Cellz(SetCnt%).SetNbr = x%
Cellz(SetCnt%).Val1 = Left(ActiveCell.Value, 1)
Cellz(SetCnt%).Val2 = Mid(ActiveCell.Value, 2, 1)
Cellz(SetCnt%).Val3 = Right(ActiveCell.Value, 1)
Cellz(SetCnt%).Addr = ActiveCell.Address
x% = x% + 1
ActiveCell.Offset(1, 0).Activate
Loop
'Start iterative loops through all the sets.
For aa% = 0 To (SetCnt% - 1)
For bb% = (aa% + 1) To SetCnt%
If TestSets(aa%, bb%) = True Then
MsgBox "A solution was found: " & Cellz(aa%).Addr & "=" &
Range(Cellz(aa%).Addr) & _
" and " & Cellz(bb%).Addr & "=" &
Range(Cellz(bb%).Addr)
Exit Sub
End If
Next bb%
Next aa%
MsgBox "No solution was found."
ReDim Cellz(0)
End Sub

Public Function TestSets(SetA As Integer, SetB As Integer) As Boolean
'Do some test  on the two sets to determine if they are the right two. I'm just
'adding the two 3-digit numbers to see if they equal a certain sum.
If (Int(Cellz(SetA).Val1 & Cellz(SetA).Val2 & Cellz(SetA).Val3) + _
Int(Cellz(SetB).Val1 & Cellz(SetB).Val2 & Cellz(SetB).Val3)) = 614 Then
TestSets = True
Exit Function
Else
TestSets = False
Exit Function
End If
End Function

If you get the other approach (array of subscripts, trinary counting) working, I
would love to see it.

Share: