Find next
 

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

Find next
 

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

Find next
 

Note: SearchAgainBtn_Click() below should be in the code module for resultform.

Public FirstFind As Range, PriorFind As Range

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 f Is Nothing Then 'Firstname found .....
' Get data from this row i.e. containing firstname
r = f.Row
FName = Cells(r, "A")
LastName = Cells(r, "B")
Phonenumber = Cells(r, "C")
Mobile = Cells(r, "D")
Location = Cells(r, "E")
Set FirstFind = f
Set PriorFind = f
Else
MsgBox "No data found"
End If
End With
End Sub

Private Sub SearchAgainBtn_Click()

If FirstFind Is Nothing Then
MsgBox "ERROR: Initial search not completed"
Else
With Worksheets("Employees").Range("a2:a500")
Set f = .FindNext(PriorFind)
If (Not f Is Nothing) And Not (f.Address = FirstFind.Address) Then
r = f.Row
FName = Cells(r, "A")
LastName = Cells(r, "B")
Phonenumber = Cells(r, "C")
Mobile = Cells(r, "D")
Location = Cells(r, "E")
Set PriorFind = f
Else
MsgBox "No other entries found for " & FirstName
Set FirstFind = Nothing
Set PriorFind = Nothing
End If
End With
End If

End Sub
--
- K Dales


"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

Find next
 

Hi Jim,

Thanks for your answer. I get your point and i put your code in a module and
try to run this but i need some more help..
I didn't change the code of searchbutton (is that correct?) and now i need
to know what code i need to put under the findnext button on the second
userform.
Now i don't get any result...
Hope you can help me some more.

"Jim Thomlinson" schreef:

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

Find next
 

Sorry about taking so long to get back to you... Work and all... The person
has to fill out a text box of whom to search for... When this text box looses
focus or the button is pressed run this code. The first item (0) in the array
is what you want to display in your first form. After that when you spawn the
second form you want to display items 1 through ??? If it was me doing the
code I would probably only use 1 form (easier for the users to keep track
of). When search is pressed I would (if there was more than 1 item in the
array) change the button caption to Search Next. When the Search Next was
pressed I would display the next item in the list...
--
HTH...

Jim Thomlinson


"Frank" wrote:

Hi Jim,

Thanks for your answer. I get your point and i put your code in a module and
try to run this but i need some more help..
I didn't change the code of searchbutton (is that correct?) and now i need
to know what code i need to put under the findnext button on the second
userform.
Now i don't get any result...
Hope you can help me some more.

"Jim Thomlinson" schreef:

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