Snasui Excel Forum

Code: Select all

If Not ComboBox1.Value = Empty Then
    sCrit = UCase(Me.ComboBox1)
    With Me.ListBox1
        .ColumnCount = 9
        .ColumnWidths = "80;80;80;0;0;110;80;0;30"
        .List = rngSource.Cells.Value
        For a = .ListCount - 1 To 0 Step -1
            If sCrit <> UCase(.List(a, 1)) Then
                .RemoveItem a
            End If
        Next a
    End With
End If

Code: Select all

Private Sub CommandButton1_Click()
Dim a As Long
Dim sCrit As String
Dim tCrit, uCrit As Long
Dim vCrit, wCrit As Date
Dim rngSource As Range
Set rngSource = Worksheets("Sheet2").Range("A2:I1000")
 
 '1.name
 If Not ComboBox1.Value = Empty Then
sCrit = "*" & UCase(Me.ComboBox1) & "*"
With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "80;80;80;0;0;110;80;0;30"
.List = rngSource.Cells.Value
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 1)) Like sCrit Then
.RemoveItem a
End If
Next a
End With
End If

Code: Select all

'3. between two dates

If Not TextBox1.Value = Empty Then
    vCrit = Application.Text(TextBox1.Value, "[$- ]dd/mm/yyyy")
    With Me.ListBox1
        .ColumnCount = 9
        .ColumnWidths = "80;80;80;0;0;110;80;0;30"
        .List = rngSource.Cells.Value
        For a = .ListCount - 1 To 0 Step -1
            If Not Application.Text(.List(a, 6), "[$- ]dd/mm/yyyy") >= vCrit Then
            .RemoveItem a
            End If
        Next a
    End With

    If Not TextBox1 = Empty Then
        wCrit = Application.Text(TextBox2.Value, "[$- ]dd/mm/yyyy")
        With Me.ListBox1
        For a = .ListCount - 1 To 0 Step -1
            If Not Application.Text(.List(a, 6), "[$- ]dd/mm/yyyy") <= wCrit Then
                .RemoveItem a
            End If
        Next a
        End With
    End If
End If

Code: Select all

Dim a As Long
Dim sCrit As String
Dim tCrit, uCrit As Long
Dim vCrit, wCrit As Date
Dim rngSource As Range
Set rngSource = Worksheets("Sheet2").Range("A2:I1000")


'3. between two dates

If Not TextBox1.Value = Empty Then
vCrit = TextBox1.Value
With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "80;80;80;0;0;110;80;0;30"
.List = rngSource.Cells.Value
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 6)) >= vCrit Then
.RemoveItem a
End If
Next a
End With

If Not TextBox1 = Empty Then
wCrit = TextBox2.Value
With Me.ListBox1
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 6)) <= wCrit Then
.RemoveItem a
End If
Next a
End With
End If
End If

Code: Select all

If ComboBox3.Value <= deg7.Value and deg7.Value <= ComboBox4.Value Then

Code: Select all

If ComboBox3.Value <= deg7.Value <= ComboBox4.Value & "*" Then

Code: Select all

'ID1<ID2

If ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value < ComboBox4.Value Then

For sat = 2 To Sheets("Sheet1").Cells(10000, "a").End(xlUp).Row
Set deg7 = Sheets("Sheet1").Cells(sat, "a")

If ComboBox3.Value <= deg7.Value <= ComboBox4.Value & "*" Then


ListBox1.AddItem
ListBox1.List(s, 0) = Sheets("Sheet1").Cells(sat, "A")
ListBox1.List(s, 1) = Sheets("Sheet1").Cells(sat, "B")
ListBox1.List(s, 2) = Sheets("Sheet1").Cells(sat, "C")
ListBox1.List(s, 3) = Sheets("Sheet1").Cells(sat, "D")
ListBox1.List(s, 4) = Sheets("Sheet1").Cells(sat, "E")
ListBox1.List(s, 5) = Sheets("Sheet1").Cells(sat, "F")
ListBox1.List(s, 6) = Sheets("Sheet1").Cells(sat, "G")
ListBox1.List(s, 7) = Sheets("Sheet1").Cells(sat, "H")

s = s + 1

End If: Next

End If

Code: Select all

    'case 3 name & surname search
    deg4 = ComboBox1.Text & ComboBox2.Text
    
    If ComboBox1.Text <> "" And ComboBox2.Text <> "" Then
        For sat2 = 2 To Sheets("Sheet1").Cells(10000, "b").End(xlUp).Row
            deg1 = Sheets("Sheet1").Cells(sat2, "b") & Sheets("Sheet1").Cells(sat2, "c")
            If UCase(deg1) Like UCase(deg4) & "*" Then
                ListBox1.AddItem
                ListBox1.List(s, 0) = Sheets("Sheet1").Cells(sat2, "A")
                ListBox1.List(s, 1) = Sheets("Sheet1").Cells(sat2, "B")
                ListBox1.List(s, 2) = Sheets("Sheet1").Cells(sat2, "C")
                ListBox1.List(s, 3) = Sheets("Sheet1").Cells(sat2, "D")
                ListBox1.List(s, 4) = Sheets("Sheet1").Cells(sat2, "E")
                ListBox1.List(s, 5) = Sheets("Sheet1").Cells(sat2, "F")
                ListBox1.List(s, 6) = Sheets("Sheet1").Cells(sat2, "G")
                ListBox1.List(s, 7) = Sheets("Sheet1").Cells(sat2, "H")
                s = s + 1
            End If
        Next
    End If

Code: Select all

Private Sub CommandButton1_Click()


Dim sat, sat2, s As Integer
Dim deg1, deg2, deg3, deg4 As String

With ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "80;80;80;0;0;110;80;0"
End With

deg2 = ComboBox1.Value


'case 1 name search
If ComboBox2.Value = "" Then


For sat = 2 To Sheets("Sheet1").Cells(10000, "b").End(xlUp).Row
Set deg1 = Sheets("Sheet1").Cells(sat, "b")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Sheets("Sheet1").Cells(sat, "A")
ListBox1.List(s, 1) = Sheets("Sheet1").Cells(sat, "B")
ListBox1.List(s, 2) = Sheets("Sheet1").Cells(sat, "C")
ListBox1.List(s, 3) = Sheets("Sheet1").Cells(sat, "D")
ListBox1.List(s, 4) = Sheets("Sheet1").Cells(sat, "E")
ListBox1.List(s, 5) = Sheets("Sheet1").Cells(sat, "F")
ListBox1.List(s, 6) = Sheets("Sheet1").Cells(sat, "G")
ListBox1.List(s, 7) = Sheets("Sheet1").Cells(sat, "H")



s = s + 1

End If: Next

End If


deg3 = ComboBox2.Value

'case 2 surname search
If ComboBox1.Value = "" Then


For sat = 2 To Sheets("Sheet1").Cells(10000, "c").End(xlUp).Row
Set deg1 = Sheets("Sheet1").Cells(sat, "c")
If UCase(deg1) Like UCase(deg3) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Sheets("Sheet1").Cells(sat, "A")
ListBox1.List(s, 1) = Sheets("Sheet1").Cells(sat, "B")
ListBox1.List(s, 2) = Sheets("Sheet1").Cells(sat, "C")
ListBox1.List(s, 3) = Sheets("Sheet1").Cells(sat, "D")
ListBox1.List(s, 4) = Sheets("Sheet1").Cells(sat, "E")
ListBox1.List(s, 5) = Sheets("Sheet1").Cells(sat, "F")
ListBox1.List(s, 6) = Sheets("Sheet1").Cells(sat, "G")
ListBox1.List(s, 7) = Sheets("Sheet1").Cells(sat, "H")


s = s + 1

End If: Next

End If

'case 3 name & surname search


deg4 = ComboBox1.Value & ComboBox2.Value

If ComboBox1.Value = Value And ComboBox2.Value = Value Then


For sat2 = 2 To Sheets("Sheet1").Cells(10000, "b" & "c").End(xlUp).Row
Set deg1 = Sheets("Sheet1").Cells(sat2, "b" & "c")
If UCase(deg1) Like UCase(deg4) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Sheets("Sheet1").Cells(sat2, "A")
ListBox1.List(s, 1) = Sheets("Sheet1").Cells(sat2, "B")
ListBox1.List(s, 2) = Sheets("Sheet1").Cells(sat2, "C")
ListBox1.List(s, 3) = Sheets("Sheet1").Cells(sat2, "D")
ListBox1.List(s, 4) = Sheets("Sheet1").Cells(sat2, "E")
ListBox1.List(s, 5) = Sheets("Sheet1").Cells(sat2, "F")
ListBox1.List(s, 6) = Sheets("Sheet1").Cells(sat2, "G")
ListBox1.List(s, 7) = Sheets("Sheet1").Cells(sat2, "H")

s = s + 1

End If: Next

End If


End Sub