Change Macro?

[Alen32]

I got this macro here which works well:
Private Sub ListBox1_Change()
With Me.ListBox1
If .ListIndex > -1 Then
'MsgBox .List(.ListIndex, 1)
ThisWorkbook.FollowHyperlink _
Address:="Http://" & .List(.ListIndex, 1)
End If
End With
End Sub

Private Sub UserForm_Initialize()
Dim cell As Range
Dim sh As Worksheet

With Me
.ListBox1.RowSource = ""
.ListBox1.ColumnCount = 3
.ListBox1.Clear
End With

Set sh = Worksheets("Ark1")

For Each cell In sh.Range("A10:A250")
If cell.Value = sh.Range("A5").Value Then
With UserForm
.ListBox1.AddItem cell.Value
.ListBox1.List(.ListBox1.ListCount - 1, 1) _
= cell.Offset(0, 2).Value
.ListBox1.List(.ListBox1.ListCount - 1, 2) _
= cell.Offset(0, 5).Value
End With
End If
Next

End Sub


I want to change macro becauseof this:
I have made Useform with 2 frame and inside of each frame two option
button. I have also one textbox.
At the end I have one commandbutton (FIND). I want to do this:
When USER make choise in this two frames and make input in textbox. And
push commanbutton(FIND) then if this 3 words appear in samme row then I
want row display in listbox. It sholud be searched in a whole workbook.

 

[Tom Ogilvy]

Look in help at the FindNext method of the range object. The sample code
shows how to search for multiple occurances of a target. Look for one of
your cells. Then each time you find it, check the two other cells in that
row for the specific condition which must be satisfied. If the conditions
are met, do the additem. continue the search.

 

[Alen32]

I found this in help but how to insert 3 words from my userform instead of
number 2?

This example finds all cells in the range A1:A500 that contain the value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Bob Phillips]

Is that search for an amalgam of the 3 words, one after the other, all three
in related cells (if so, what is the relationship), or any one of the three?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Alen32]

I make database of my articles in excell. User can choose with
optionbutton.
frame 1
horse
pig
frame2
danish
foreign
and
text box is free but only one word.
Now you have tree words ex. horse, danish and Aminoacids (input ex. from
textbox).
this three words can be found in samme row and I want to display these row
in listbox.
Words are in samme row but in different columns.

 

[Tom Ogilvy]

With userform1
if .optionButton1 then
sOne = "Horse"
else
sOne = "Pig"
end if
if .optionButton3 then
sTwo = "Danish"
else
sTwo = "Foreign"
end if
sThree = .Textbox1.Text
End With
With Worksheets(1).Cells
Set c = .Find(sOne, 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
' add row to listbox
end if
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Alen32]

I tried everything but I can't add rows to listbox.