ComboBox Search

[Fiske]

Hi i am writing a program which where the user will search data base on the value in the dropdown combo box in the userform.
The user can only select the options base on the combo box, there are 3 of them

1st: the type of ship: it will determine which sheet it will open
2nd: Tonnage: which will be a range eg. bigger than 10000, lesser than 20000
3rd: Speed : which be a range eg. Bigger than 15 lesser than 20000

thus it will create a new sheet and paste the found data ( the whole row whoch meets the requirement there)

Any help?
Thanks!

 

[My Aswer Is This]

OK:
So combobox1.value= "Cargo"
We will search Column "A" of sheet named "Cargo"

For the value selected in Combobox2

And the value from combobox3 must be in Column B of sheet named "Cargo" same row

And when found will create a new sheet named "Result" and copy the row found in sheet named Cargo to row(1) of sheet named "Result"

Is this what your wanting?

If not then give more specific details.

 

[Fiske]

Yep!

 

[My Aswer Is This]

Try using these tow scripts in your userform:

Private Sub CommandButton1_Click()
'Modified 6/13/18 1:00 AM EDT
On Error GoTo M
Dim i As Long
Dim x As Long
x = 0
Dim ans As String
Dim anss As String
Dim ansss As String
Dim Lastrow As Long
ans = ComboBox1.Value
anss = ComboBox2.Value
ansss = ComboBox3.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
    If Sheets(ans).Cells(i, 1).Value = anss And Sheets(ans).Cells(i, 2).Value = ansss Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Results"
        Sheets(ans).Rows(i).Copy Sheets("Results").Rows(1)
        MsgBox "Sheet added and row copied"
        x = x + 1
    End If
Next
If x = 0 Then MsgBox "No such value found"
Exit Sub
M:
MsgBox "We had some sort of problem" & vbNewLine & "Maybe no such sheet or You have Created two sheets with the same name"
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
    For i = 1 To Sheets.Count
    ComboBox1.AddItem Sheets(i).Name
    Next
End Sub

 

[Fiske]

the code works great but what if my condition in the combo box is a range of values?
lets say in combo box 2, it will have <15, 15-20, 20>? as the values in column b will have 16 or 17 which meet the criteria.

 

[My Aswer Is This]

You plan to put this in combobox 2:

<15, 15-20, 20>​

And you want to search for:
any thing less then 15
Or:
15 to 20

or greater then 20

I do not understand

 

[Fiske]

yup,
So lets say in combobox 2 i want to put <15, 15-20, 20>
so if the user choose <15, it will search anything less than 15, 15-20, search anything between 15-20 inclusive and 20>, anything more than 20.

 

[Fluff]

Maybe something like

Private Sub CommandButton1_Click()
   Dim sp1, sp2
   With Sheets("Pcode")
      If .AutoFilterMode Then .AutoFilterMode = False
      sp1 = Split(Me.ComboBox2, "-")
      sp2 = Split(Me.ComboBox3, "-")
      
      .Range("A1:Z1").AutoFilter 1, Me.ComboBox1.Value
      If UBound(sp1) > 0 Then
         .Range("A1:Z1").AutoFilter 2, ">=" & sp1(0), xlAnd, "<=" & sp1(1)
      Else
         .Range("A1:Z1").AutoFilter 2, sp1(0)
      End If
      If UBound(sp2) > 0 Then
         .Range("A1:Z1").AutoFilter 3, ">=" & sp2(0), xlAnd, "<=" & sp2(1)
      Else
         .Range("A1:Z1").AutoFilter 3, sp2(0)
      End If
      If Not Evaluate("isref(Result!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).name = "Result"
      .AutoFilter.Range.Copy Sheets("Result").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[Fiske]

Maybe something like

Private Sub CommandButton1_Click()
   Dim sp1, sp2
   With Sheets("Pcode")
      If .AutoFilterMode Then .AutoFilterMode = False
      sp1 = Split(Me.ComboBox2, "-")
      sp2 = Split(Me.ComboBox3, "-")
      
      .Range("A1:Z1").AutoFilter 1, Me.ComboBox1.Value
      If UBound(sp1) > 0 Then
         .Range("A1:Z1").AutoFilter 2, ">=" & sp1(0), xlAnd, "<=" & sp1(1)
      Else
         .Range("A1:Z1").AutoFilter 2, sp1(0)
      End If
      If UBound(sp2) > 0 Then
         .Range("A1:Z1").AutoFilter 3, ">=" & sp2(0), xlAnd, "<=" & sp2(1)
      Else
         .Range("A1:Z1").AutoFilter 3, sp2(0)
      End If
      If Not Evaluate("isref(Result!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).name = "Result"
      .AutoFilter.Range.Copy Sheets("Result").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

Hi tried yours but it only copied the header cell (first row) and not the data found.

Private Sub CommandButton1_Click() Dim sp1, sp2, sp3
With Sheets("RORO")
If .AutoFilterMode Then .AutoFilterMode = False
sp1 = Split(Me.ComboBox2, "-")
sp2 = Split(Me.ComboBox3, "-")
sp3 = Split(Me.ComboBox4, "-")


.Range("A1:Z1").AutoFilter 1, Me.ComboBox1.Value
If UBound(sp1) > 19 Then
.Range("H1:H100").AutoFilter 2, ">=20" & sp1(0), xlAnd, "30<=" & sp1(1)
Else
.Range("H1:H100").AutoFilter 2, sp1(0)
End If
If UBound(sp2) > 59999 Then
.Range("M1:M100").AutoFilter 3, ">=10000" & sp2(0), xlAnd, "60000<=" & sp2(1)
Else
.Range("M1:M100").AutoFilter 3, sp2(0)
End If
If UBound(sp3) > 19 Then
.Range("O1:O100").AutoFilter 3, ">=20" & sp3(0), xlAnd, "30<=" & sp3(1)
Else
.Range("O1:O100").AutoFilter 3, sp3(0)
End If

If Not Evaluate("isref(Result!A2)") Then Sheets.Add(, Sheets(Sheets.Count)).Name = "Result"
.AutoFilter.Range.Copy Sheets("Result").Range("A2")
.AutoFilterMode = False
End With
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Sheets.Count
'Type Of Ship
ComboBox1.AddItem Sheets(i).Name
Next
'Crew
ComboBox2.List = Array("<20", "20-30", ">20")
'Gross Tonnage
ComboBox3.List = Array("<10000", "10000-60000", ">60000")
'Max speed
ComboBox4.List = Array("<20", "20-30", ">20")


End Sub

 

[Fluff]

It should be like

Private Sub CommandButton1_Click()
   Dim sp1, sp2, sp3
   With Sheets("Pcode")
      If .AutoFilterMode Then .AutoFilterMode = False
      sp1 = Split(Me.ComboBox2, "-")
      sp2 = Split(Me.ComboBox3, "-")
      sp3 = Split(Me.ComboBox4, "-")
      
      .Range("A1:Z1").AutoFilter 1, Me.ComboBox1.Value
      If UBound(sp1) > 0 Then
         .Range("A1:Z1").AutoFilter 8, ">=" & sp1(0), xlAnd, "<=" & sp1(1)
      Else
         .Range("A1:Z1").AutoFilter 8, sp1(0)
      End If
      If UBound(sp2) > 0 Then
         .Range("A1:Z1").AutoFilter 13, ">=" & sp2(0), xlAnd, "<=" & sp2(1)
      Else
         .Range("A1:Z1").AutoFilter 13, sp2(0)
      End If
      If UBound(sp3) > 0 Then
         .Range("A1:Z1").AutoFilter 15, ">=" & sp3(0), xlAnd, "<=" & sp3(1)
      Else
         .Range("A1:Z1").AutoFilter 15, sp3(0)
      End If

      If Not Evaluate("isref(Result!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).name = "Result"
      .AutoFilter.Range.Copy Sheets("Result").Range("A2")
      .AutoFilterMode = False
   End With
End Sub

assuming you are filtering on cols A,H,M & O