Welcome Guest
The time is now 4:49 pm
You last visited January 22, 2017, 3:13 pm
All times shown are
Eastern Time (GMT-5:00)

# Pick3 Simple Builder

Published:

Option Explicit

Sub Pick3__Simple__Builder()
'by time*treat'
'input a starting list/table of pick 3 numbers as digit source'
'output a list of ONLY 6-way numbers'
'Single digit per cell, all on Sheet1'

'input: Starting at Row 1, Cols 1~3'
'one space gap'
'output: Starting at Row 1, Cols 5~7'

Dim first__combo As Integer, last__combo As Integer, active__combo As Integer
Dim active__digits() As Integer, t As Integer
Dim p3__x As Integer, p3__y As Integer, p3__z As Integer
Dim input__row As Integer, input__col As Integer, output__row As Integer

'initialize'
output__row = 0
ReDim active__digits(0 To 9)
For t = 0 To 9
active__digits(t) = 0
Next t

'get list/table'
input__row = 1
While Sheet1.Cells(input__row, 1).Value <> ""
For input__col = 1 To 3
active__digits(Sheet1.Cells(input__row, input__col).Value) = 1
Next input__col
input__row = input__row + 1
Wend

'build output'
first__combo = 0
last__combo = 999 'or 789'

For active__combo = first__combo To last__combo
p3__x = Int(active__combo / 100) 'hundreds'
p3__y = Int((active__combo Mod 100) / 10) 'tens'
p3__z = active__combo Mod 10  'ones'

'check for exists'
If active__digits(p3__x) <> 1 Then GoTo 99
If active__digits(p3__y) <> 1 Then GoTo 99
If active__digits(p3__z) <> 1 Then GoTo 99

'check for order'
If p3__x >= p3__y Then GoTo 99
If p3__x >= p3__z Then GoTo 99
If p3__y >= p3__z Then GoTo 99

'all good'
output__row = output__row + 1
Sheet1.Cells(output__row, 5).Value = p3__x
Sheet1.Cells(output__row, 6).Value = p3__y
Sheet1.Cells(output__row, 7).Value = p3__z

99:   'early exit'
Next active__combo
End Sub

Entry #698