A
Alen32
I got this code here which work well. This code search for 3 words in same
row and if it find some row then display them in listbox. I have problem
when I have two word ex. protein and rawprotein in same row and searchword
is protein then prgram doesn't display that row. If I remove one of this
words from row then program display that row.
Code here:
Private Sub CommandButton1_Click()
Dim sOne, sOneItem
With Me
.ListBox1.RowSource = ""
.ListBox1.ColumnCount = 3
.ListBox1.Clear
End With
With UserForm1
'******
If .OptionButton1 Then sOne = Array("Hest")
If .OptionButton2 Then sOne = Array("Grise")
If .OptionButton5 Then sOne = Array("Hest", "Grise")
'******
If .OptionButton3 Then
sTwo = "Artikel"
Else
sTwo = "Figur"
End If
sThree = .TextBox1.Text
End With
'******
For Each sOneItem In sOne
'******
'MsgBox sOneItem
With Worksheets(1).Cells
Set C = .Find(sOneItem, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Application.CountIf(C.EntireRow, "*" & sTwo & "*") And _
Application.CountIf(C.EntireRow, "*" & sThree & "*")
Then
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 0) _
= C.Offset(0, -3).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 1) _
= C.Offset(0, -1).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 2) _
= C.Offset(0, 4).Value
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
'******
Next
'******
End Sub
row and if it find some row then display them in listbox. I have problem
when I have two word ex. protein and rawprotein in same row and searchword
is protein then prgram doesn't display that row. If I remove one of this
words from row then program display that row.
Code here:
Private Sub CommandButton1_Click()
Dim sOne, sOneItem
With Me
.ListBox1.RowSource = ""
.ListBox1.ColumnCount = 3
.ListBox1.Clear
End With
With UserForm1
'******
If .OptionButton1 Then sOne = Array("Hest")
If .OptionButton2 Then sOne = Array("Grise")
If .OptionButton5 Then sOne = Array("Hest", "Grise")
'******
If .OptionButton3 Then
sTwo = "Artikel"
Else
sTwo = "Figur"
End If
sThree = .TextBox1.Text
End With
'******
For Each sOneItem In sOne
'******
'MsgBox sOneItem
With Worksheets(1).Cells
Set C = .Find(sOneItem, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Application.CountIf(C.EntireRow, "*" & sTwo & "*") And _
Application.CountIf(C.EntireRow, "*" & sThree & "*")
Then
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 0) _
= C.Offset(0, -3).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 1) _
= C.Offset(0, -1).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 2) _
= C.Offset(0, 4).Value
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
'******
Next
'******
End Sub