Stopping a Loop
 

This is a great formum. Hope I don't wear out my welcome.

I have a form which is filled based upon a search of a specific string in a
specific column i.e. 455-203-23. On that form there is a commnad button that
calls an addtional search of the database to see if there are other records
with the same string exist in the database. It works great, however, I can't
get it to stop once its gone through the entire database as it simply goes
back to the first record in the database an starts over. Any suggestions as
to how to break the code once I've reached the last row of the database which
can change from time to time.

Private Sub FindNext_Click()
Dim databaseRow As Long
Set devdataSheet = Sheets("DevData")
Application.ScreenUpdating = False

APNNumber = InputForm.TextBox1
devdataSheet.Activate
devdataSheet.Range("BZ" & databaseRow).Select
With devdataSheet
Set FoundCell = .Cells.Find(what:=APNNumber, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlDown, _
MatchCase:=True)
If FoundCell Is Nothing Then
MsgBox " APN Not Found"
Else
devdataSheet.Activate
Project = devdataSheet.Range("A" & FoundCell.Row).Text
Me.LoadLongInfo3 Project
End If
End With
End Sub

Stopping a Loop
 

maybe looking at findnext in vb help will help you:

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


--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
This is a great formum. Hope I don't wear out my welcome.

I have a form which is filled based upon a search of a specific string in a
specific column i.e. 455-203-23. On that form there is a commnad button that
calls an addtional search of the database to see if there are other records
with the same string exist in the database. It works great, however, I can't
get it to stop once its gone through the entire database as it simply goes
back to the first record in the database an starts over. Any suggestions as
to how to break the code once I've reached the last row of the database which
can change from time to time.

Private Sub FindNext_Click()
Dim databaseRow As Long
Set devdataSheet = Sheets("DevData")
Application.ScreenUpdating = False

APNNumber = InputForm.TextBox1
devdataSheet.Activate
devdataSheet.Range("BZ" & databaseRow).Select
With devdataSheet
Set FoundCell = .Cells.Find(what:=APNNumber, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlDown, _
MatchCase:=True)
If FoundCell Is Nothing Then
MsgBox " APN Not Found"
Else
devdataSheet.Activate
Project = devdataSheet.Range("A" & FoundCell.Row).Text
Me.LoadLongInfo3 Project
End If
End With
End Sub

Stopping a Loop
 

This is just a guess, since you've only posted a code snippet-

Near the end of your Sub, you call another procedu
Me.LoadLongInfo3 Project

does that procedure call /this/ procedure again, or loop to another
procedure that loops back to Me.LoadLongInfo3 Project?

HTH,
Keith

"RussellT" wrote:

This is a great formum. Hope I don't wear out my welcome.

I have a form which is filled based upon a search of a specific string in a
specific column i.e. 455-203-23. On that form there is a commnad button that
calls an addtional search of the database to see if there are other records
with the same string exist in the database. It works great, however, I can't
get it to stop once its gone through the entire database as it simply goes
back to the first record in the database an starts over. Any suggestions as
to how to break the code once I've reached the last row of the database which
can change from time to time.

Private Sub FindNext_Click()
Dim databaseRow As Long
Set devdataSheet = Sheets("DevData")
Application.ScreenUpdating = False

APNNumber = InputForm.TextBox1
devdataSheet.Activate
devdataSheet.Range("BZ" & databaseRow).Select
With devdataSheet
Set FoundCell = .Cells.Find(what:=APNNumber, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlDown, _
MatchCase:=True)
If FoundCell Is Nothing Then
MsgBox " APN Not Found"
Else
devdataSheet.Activate
Project = devdataSheet.Range("A" & FoundCell.Row).Text
Me.LoadLongInfo3 Project
End If
End With
End Sub