This code will return an array of the people you are looking for. This code
needs to be placed in a module. If the no matches are found then the function
returns nothing.

Public Type Person
FirstName As String
LastName As String
Phone As String
Mobile As String
Location As String
End Type

Sub test()
Dim aryTest() As Person

aryTest = People(Sheets("Sheet1").Range("A1:A20"), "Dave")
End Sub



Public Function People(ByVal Target As Range, ByVal ToFind As String) As
Person()
Dim rngFound As Range
Dim rngFirst As Range
Dim aryResult() As Person
Dim lngCounter As Long

Set rngFound = Target.Find(ToFind, , xlValues)
If Not rngFound Is Nothing Then

ReDim Preserve aryResult(lngCounter)
aryResult(lngCounter).FirstName = rngFound.Value
aryResult(lngCounter).LastName = rngFound.Offset(0, 1).Value
aryResult(lngCounter).Phone = rngFound.Offset(0, 2).Value
aryResult(lngCounter).Mobile = rngFound.Offset(0, 3).Value
aryResult(lngCounter).Location = rngFound.Offset(0, 4).Value
lngCounter = lngCounter + 1
Set rngFirst = rngFound
Set rngFound = Target.FindNext(rngFound)
Do While rngFound.Address < rngFirst.Address
ReDim Preserve aryResult(lngCounter)
aryResult(lngCounter).FirstName = rngFound.Value
aryResult(lngCounter).LastName = rngFound.Offset(0, 1).Value
aryResult(lngCounter).Phone = rngFound.Offset(0, 2).Value
aryResult(lngCounter).Mobile = rngFound.Offset(0, 3).Value
aryResult(lngCounter).Location = rngFound.Offset(0, 4).Value
lngCounter = lngCounter + 1
Set rngFound = Target.FindNext(rngFound)
Loop
People = aryResult
End If
End Function

--
HTH...

Jim Thomlinson


"Frank" wrote:

Hi,

I have created an option to search for a name in a userform and after you
press search a new userform opens which shows the result. So far it works
fine. On the second userform i have a button called Next result. I want to
show the next result but i can't figure it out. Can some one have a look at
this?

The code used for the search option:

Private Sub Zoekbtn_Click()
If Searchform.firstname = "" Then

msgbox "Enter a value"
Else
Call firstnamesearch
Resultform.Show
End If
End Sub

Private Sub firstnamesearch()

With Worksheets("Employees").Range("a2:a500")
Set f = .Find(Firstname, LookIn:=xlValues)
If Not c Is Nothing Then 'Firstname found .....
' Get data from this row i.e. containing firstname
r = f.Row
Resultform.Firstname = Cells(r, "A")
Resultform.Lastname = Cells(r, "B")
Resultform.Phonenumber = Cells(r, "C")
Resultform.Mobile = Cells(r, "D")
Resultform.Location = Cells(r, "E")


Else
msgbox "No data found"

End If
End With
End Sub