Private Sub CommandButton1_Click()
'SEARCH
Dim c As Variant
Dim Col As Variant
Dim Data As Variant
Dim FirstAddx As String
Dim FoundIt As Range
Dim I As Integer
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
Dim SrcWks As Worksheet
Dim strFind As String 'what to find
Dim x As Long
Set SrcWks = Worksheets("Membership Sales")
Set MyData = SrcWks.Range("A2").CurrentRegion
With Frame1.Controls
For I = 0 To .Count - 1
If .Item(I).Value = True Then
BtnName = .Item(I).Name
Exit For
End If
Next I
End With
Select Case BtnName
Case "OptionButton1"
strFind = TextBox1
x = 0 - 4
Col = 5: Data = TextBox1: GoSub DataSearch
'Col = 3: Data = TextBox2: GoSub DataSearch
Case "OptionButton2"
strFind = TextBox1
x = 0 - 5
Col = 6: Data = TextBox1: GoSub DataSearch
Case "OptionButton3"
strFind = TextBox1
'x = 0 - 5
Col = 1: Data = TextBox1: GoSub DataSearch
Case "OptionButton4"
strFind = TextBox1
x = 0 - 19
Col = 20: Data = TextBox1: GoSub DataSearch
End Select
Exit Sub
DataSearch:
With SrcWks
Set Rng = .Cells(2, Col)
Set RngEnd = .Cells(Rows.Count, Col).End(xlUp)
Set RngEnd = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd)
Set Rng = .Range(Rng, RngEnd)
End With
Data = Trim(Data)
Set FoundIt = Rng.Find(What:=Data, After:=Rng.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=CheckBox2.Value + 2, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=CheckBox1.Value)
If Not FoundIt Is Nothing Then
FirstAddx = FoundIt.Address
If CheckBox3.Value = False Then Exit Sub
Set FoundIt = Rng.FindNext(FoundIt)
R = R + 1
With SrcWks
If Not .AutoFilterMode Then MyData.AutoFilter
MyData.AutoFilter Field:=Col, Criteria1:=strFind
Set Rng = MyData.Columns(Col).Cells.SpecialCells(xlCellTypeVisible)
Me.ListBox1.Clear
For Each c In Rng
With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 0) = c.Offset(0, x).Address 'record number
.List(.ListCount - 1, 1) = c.Offset(0, x).Value 'surname
.List(.ListCount - 1, 2) = c.Offset(0, (x + 1)).Value 'first name
.List(.ListCount - 1, 3) = c.Offset(0, (x + 2)).Value 'DOB
.List(.ListCount - 1, 4) = c.Offset(0, (x + 3)).Value 'address
.List(.ListCount - 1, 5) = c.Offset(0, (x + 4)).Value 'date spoken to
.List(.ListCount - 1, 6) = c.Offset(0, (x + 5)).Value 'notes
.List(.ListCount - 1, 7) = c.Offset(0, (x + 6)).Value
.List(.ListCount - 1, 8) = c.Offset(0, (x + 7)).Value
.List(.ListCount - 1, 9) = c.Offset(0, (x + 8)).Value
'.List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value
'.List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value
'.List(.ListCount - 1, 12) = c.Offset(0, (x + 11)).Value
'.List(.ListCount - 1, 13) = c.Offset(0, (x + 12)).Value
'.List(.ListCount - 1, 14) = c.Offset(0, (x + 13)).Value
'.List(.ListCount - 1, 15) = c.Offset(0, (x + 14)).Value
'.List(.ListCount - 1, 16) = c.Offset(0, (x + 15)).Value
'.List(.ListCount - 1, 17) = c.Offset(0, (x + 16)).Value
'.List(.ListCount - 1, 18) = c.Offset(0, (x + 17)).Value
'.List(.ListCount - 1, 19) = c.Offset(0, (x + 18)).Value
'.List(.ListCount - 1, 20) = c.Offset(0, (x + 19)).Value
'.List(.ListCount - 1, 21) = c.Offset(0, (x + 20)).Value
End With
Next c
End With
' Me.EnableEvents = True
' Loop While FoundIt.Address <> FirstAddx And Not FoundIt Is Nothing
' Me.ListBox1.RowSource = DstWks.UsedRange.Address(external:=True)
Else
MsgBox "No Match was found for '" & Data & " '", vbExclamation
End If
SrcWks.AutoFilterMode = False
End Sub
Bookmarks