Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax for stopping a Do Loop? | Excel Discussion (Misc queries) | |||
Help stopping when row is blank | Excel Programming | |||
Stopping repetitive loop execution through user form (or other ide | Excel Programming | |||
Stopping a Macro | Excel Programming | |||
Stopping Code from Running whilst in Loop... | Excel Programming |