Is Multi-Sequence Search Selection Macro Possible?

  • Thread starter Thread starter Cecil
  • Start date Start date
C

Cecil

Hi:

This is the first time I am posting to this newsgroup and if it is not the
right place for my question, please point me to the correct newsgroup.

If I have three columns with a multiple sequences of numbers, separated by
empty cells,
is it programmatically possible to have a macro to select a specific pattern
in the three columns

that meet the following conditions:
For example using columns R, S and T.
In column S, find a vertical sequence of 1, 2, 3, 4 and 5, that is followed
by a vertical sequence of

1 through 13, directly to the left of column S, the next row down in column
R, followed by a vertical

sequence of 1, 2, 3, directly to the right of column R, the next row down in
column S, and finally

followed by a vertical sequence of 1 through 19, directly to the right of
column S, the next row down

in column T?
My goal is to have macro to find the right sequence of 40 numbers while
ignoring sequences that don't

match. A nice bonus would be if no matches are found after the search
completes, display a "Match not found", messagebox!

Any help would be appreciated!
Thanks in advance,
Cecil
 
Hi:

This is the first time I am posting to this newsgroup and if it is not the
right place for my question, please point me to the correct newsgroup.

If I have three columns with a multiple sequences of numbers, separated by
empty cells,
is it programmatically possible to have a macro to select a specific pattern
in the three columns

that meet the following conditions:
For example using columns R, S and T.
In column S, find a vertical sequence of 1, 2, 3, 4 and 5, that is followed
by a vertical sequence of

1 through 13, directly to the left of column S, the next row down in column
R, followed by a vertical

sequence of 1, 2, 3, directly to the right of column R, the next row downin
column S, and finally

followed by a vertical sequence of 1 through 19, directly to the right of
column S, the next row down

in column T?
My goal is to have macro to find the right sequence of 40 numbers while
ignoring sequences that don't

match. A nice bonus would be if no matches are found after the search
completes, display a "Match not found", messagebox!

Any help would be appreciated!
Thanks in advance,
Cecil
Click to expand...

Cecil,

I think this will work for you.

Open up VBA and add a module then paste the code below into the module

Option Explicit
Global SequenceMatch As Boolean

Public Function SequenceStartRow() As String
Dim Row1, Row2, Row3, Row4 As Integer
Dim CurrentRow As Integer
Row1 = -1
Row2 = -1
Row3 = -1
Row4 = -1
CurrentRow = 1
StartingPoint:

Row1 = CheckSequence(1, 5, CurrentRow, 19, 1, Worksheets(1))

If SequenceMatch Then
Row2 = CheckSequence(1, 13, Row1 + 1, 18, 2, Worksheets(1))
If SequenceMatch Then
Row3 = CheckSequence(1, 5, Row2 + 1, 19, 3, Worksheets(1))
If SequenceMatch Then
Row4 = CheckSequence(1, 3, Row3 + 1, 20, 4, Worksheets(1))
Else
CurrentRow = Row3
GoTo StartingPoint
End If
Else
CurrentRow = Row2
GoTo StartingPoint
End If
End If
If Row4 = -1 Then
SequenceStartRow = "Not Found"
Else
SequenceStartRow = CStr(Row1 - 4)
End If

End Function
Function CheckSequence(StartNum As Integer, EndNum As Integer,
StartRow As Integer, StartCol As Integer, SeqNum As Integer, wsheet As
Worksheet) As Integer
Dim i As Long
Dim NextNumToSearch As Integer
CheckSequence = -1
NextNumToSearch = StartNum
For i = StartRow To 65536
If wsheet.Cells(i, StartCol) = NextNumToSearch Then

NextNumToSearch = NextNumToSearch + 1
If NextNumToSearch = EndNum Then
CheckSequence = i + 1
SequenceMatch = True
Exit For
End If
Else
If SeqNum > 1 Then
CheckSequence = i
SequenceMatch = False
Exit For
End If
NextNumToSearch = StartNum
End If
Next i
End Function

The function will return the row in column S that the sequence starts
in

You can run the function by typing
=SequenceStartRow()


hth

Regards

David
 
Back
Top