A
Alen32
I have this macro which search for 3 value in same row and if it find some
then display them in listbox. I want to make one more option button and
when I check then it will search for both danish and foreign. Same
function as optionbutton3 which take bot pig and horse.
Private Sub CommandButton1_Click()
Dim sOne, sOneItem
With UserForm1
'******
If .OptionButton1 Then sOne = Array("Horse")
If .OptionButton2 Then sOne = Array("Pig")
If .OptionButton3 Then sOne = Array("Horse", "Pig")
'******
If .OptionButton4 Then
sTwo = "Danish"
Else
sTwo = "Foreign"
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 c.Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 1) _
= c.Offset(0, 2).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 2) _
= c.Offset(0, 5).Value
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
'******
Next
'******
End Sub
then display them in listbox. I want to make one more option button and
when I check then it will search for both danish and foreign. Same
function as optionbutton3 which take bot pig and horse.
Private Sub CommandButton1_Click()
Dim sOne, sOneItem
With UserForm1
'******
If .OptionButton1 Then sOne = Array("Horse")
If .OptionButton2 Then sOne = Array("Pig")
If .OptionButton3 Then sOne = Array("Horse", "Pig")
'******
If .OptionButton4 Then
sTwo = "Danish"
Else
sTwo = "Foreign"
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 c.Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 1) _
= c.Offset(0, 2).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 2) _
= c.Offset(0, 5).Value
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
'******
Next
'******
End Sub