Looping Question

[ddh]

When I enter a Name that is not in the database and it asks for another search and I type in a good name and it will find it then it will not stop.

It will keep searching after I say no to another search then it will ask again and then when I say no again it will quit.


I have been unable to insert the

If MyFindNext = vbNo Then
Exit Sub

in the correct place.
If I enter a name that is not in the database several times and then try to quit, it will not quit on the first "No".
Other then that the code works perfect.
Thank you very much.

Sub LineSearchTEST01(Optional SearchVal As String)
 Dim MyValue As String
 Dim MyFindNext As Long
 Dim sht As Worksheet
 Dim Ans As Long
 Dim FirstAddress As String
 Dim Cel As Range
 Dim Counter As Long
If SearchVal = "" Then
     MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
 Else
     MyValue = SearchVal
 End If
If MyValue = "" Then
     [C3].Select
     Exit Sub
 End If
For Each sht In Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", _
     "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
     "W", "X", "Y", "Z"))
     With sht
         sht.Activate
         Set Cel = .Columns(3).Find(What:=MyValue)
         MyFindNext = vbYes
         If Not Cel Is Nothing Then
             FirstAddress = Cel.Address
             Do
                 Counter = Counter + 1
                 Cel.Activate
                 MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
                 Set Cel = .Columns(3).FindNext(Cel)
             Loop While Not Cel Is Nothing And _
             Cel.Address <> FirstAddress And _
             MyFindNext = vbYes
         End If
     End With
     If MyFindNext = vbNo Then
         Exit Sub
     End If
 Next
If Counter = 0 Then
     Ans = MsgBox("Search could not find '" & MyValue & "'." & _
     vbNewLine & " " & vbNewLine & _
     "Try another search?", 4, MyValue & " not found")
     If Ans = vbYes Then
         Call LineSearchTEST01
     Else
         Sheets("A").Select [C3].Select
         Exit Sub
     End If
 End If
If MyFindNext = vbYes Then
     Call LineSearchTEST01(MyValue)
 End If
If MyFindNext = vbNo Then
     Exit Sub
 End If
 End Sub
 
   

[Paleo]

Hi ddh,

please use the VBA function for your code, its very hard to read it this way.

[Ken Puls]

Hi ddh,

As Carlos mentioned:

[uvba]a[/uvba]

I fixed it up for you this time.

[ddh]

I am very sorry for not doing correct and making it hard to read.

Thank you very much for fixing it and I will enter it correct the next time.

[mdmackillop]

Peculiar behavior, which I don't understand, but I see what's happening
This modification works:

MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
'This line works
If MyFindNext = vbNo Then GoTo LastLine
'This line doesn't work; goes to Exit Sub line below
'If MyFindNext = vbNo Then Exit Sub
Set Cel = .Columns(3).FindNext(Cel)
Loop While Not Cel Is Nothing And _
Cel.Address <> FirstAddress And _
MyFindNext = vbYes
End If
End With
If MyFindNext = vbNo Then
Exit Sub
End If
Next
If Counter = 0 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbYes Then
Call LineSearchTEST01
Else
Sheets("A").Select [C3].Select
Exit Sub 'Line acting as label
End If
End If
If MyFindNext = vbYes Then
Call LineSearchTEST01(MyValue)
End If
If MyFindNext = vbNo Then
Exit Sub
End If
'Line added by me.
LastLine:
End Sub

However if you change

If MyFindNext = vbNo Then GoTo LastLine

'to

If MyFindNext = vbNo Then Exit Sub

it goes to the Exit Sub line noted above, and runs the last section of your code.

[Ken Puls]

Okay, I'm kind of curious on something here... maybe related, but maybe not...

The name of the procedure is:

Sub LineSearchTEST01(Optional SearchVal As String)

And during this procedure, we have the following:

If Counter = 0 Then 
         Ans = MsgBox("Search could not find '" & MyValue & "'." & _ 
         vbNewLine & " " & vbNewLine & _ 
         "Try another search?", 4, MyValue & " not found") 
         If Ans = vbYes Then 
             Call LineSearchTEST01 
         Else 
             Sheets("A").Select [C3].Select 
             Exit Sub 
         End If 
     End If 
If MyFindNext = vbYes Then 
         Call LineSearchTE

ST01(MyValue)
End If

So... when we get to a line and call the procedure that we're running, what happens? Does it call itself? (I haven't tested it out, just looking at the code.)

Could that be why the exit sub allows it to run the last section of the code? Because it hasn't finished the intial run through the code when it hits the exit sub line?

[ddh]

Thank you for the code. It still is doing the same thing. I checked and I think I entered it in the correct place.
When I type in a name that is not in the database it will search and tell me that it can not find it and ask for another search and then it you give it a name that is in the database it will find it. Then when you try to quit it will search again and then when it stops and you tell it no it will quit.
Did I enter the line in the wrong place.
I entered the VBA codes before and after, I hope it works.

Sub LineSearchTEST01(Optional SearchVal As String)
Dim MyValue As String
Dim MyFindNext As Long
Dim sht As Worksheet
Dim Ans As Long
Dim FirstAddress As String
Dim Cel As Range
Dim Counter As Long
If SearchVal = "" Then
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
Else
MyValue = SearchVal
End If
If MyValue = "" Then
[C3].Select
Exit Sub
End If
For Each sht In Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"))
With sht
sht.Activate
Set Cel = .Columns(3).Find(What:=MyValue)
MyFindNext = vbYes
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then GoTo LastLine
Set Cel = .Columns(3).FindNext(Cel)
Loop While Not Cel Is Nothing And _
Cel.Address <> FirstAddress And _
MyFindNext = vbYes
End If
End With
If MyFindNext = vbNo Then
Exit Sub
End If
Next
If Counter = 0 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbYes Then
Call LineSearchTEST01
Else
Sheets("A").Select [C3].Select
Exit Sub
End If
End If
If MyFindNext = vbYes Then
Call LineSearchTEST01(MyValue)
End If
If MyFindNext = vbNo Then
Exit Sub
End If
LastLine:
End Sub

[Stromma]

Hi ddh

I haven't tested this so i'm not sure if it would solve your problem but why do you set "MyFindNext = vbYes before " If Not Cel Is Nothing Then" ?

For Each sht In Sheets(Array("A", "B", "C"))
With sht
sht.Activate
Set Cel = .Columns(3).Find(What:=MyValue)
MyFindNext = vbYes 'Here you set MyFindNext = vbYes
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
'Set break point at next line
If MyFindNext = vbNo Then Exit Sub '*****Code jumps from here
Set Cel = .Columns(3).FindNext(Cel)
Loop While Not Cel Is Nothing And _
Cel.Address <> FirstAddress And _
MyFindNext = vbYes
End If
End With
If MyFindNext = vbNo Then
Exit Sub
End If
Next

Removing it or putting it inisde the "If" should probably work!

Stromma

[johnske]

Hi ddh,

Does this do what you want to do?

Sub LineSearchTEST01(Optional SearchVal As String)
Dim MyValue$, MyFindNext&, Ans&
Dim FirstAddress$, Counter&
Dim sht As Worksheet, Cel As Range
If SearchVal = "" Then
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
If MyValue = "" Then
[C3].Select
Exit Sub
End If
Else
MyValue = SearchVal
End If
For Each sht In Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
"W", "X", "Y", "Z"))
With sht
sht.Activate
Set Cel = .Columns(3).Find(What:=MyValue)
Counter = 0
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then Exit Sub
'Else it's vbYes & the search continues
Set Cel = .Columns(3).FindNext(Cel)
Loop Until Cel Is Nothing Or Cel.Address = FirstAddress
End If
End With
Next
If Counter = 0 Then
Sheets(1).Select [C3].Select
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbYes Then
Call LineSearchTEST01
Else
Exit Sub
End If
End If
End Sub

[Ken Puls]

Hey folks,

Steiner came to the same conclusion I did about calling the macro from itself. (Although he phrased it better!)

See here.

I'm not sure how much of a concern this is to you, ddh, but I can see that you'd certainly end up with some unpredictable results from it.

[johnske]

Hey folks,

Steiner came to the same conclusion I did about calling the macro from itself. (Although he phrased it better!)

See here.

I'm not sure how much of a concern this is to you, ddh, but I can see that you'd certainly end up with some unpredictable results from it.

I agree, this could cause some quite unpredictable results, you'd be better off putting Start: just after the declaration of variables and then replace Call LineSearchTEST01 with, Goto Start

John

[ddh]

Johnske and Kpuls - Thank you very much for the code.

I tried it and here is what happened.
1 bad entry
1st entry - Bad Name - Try again
2nd entry - Good name - goes to it.
Tell it to quit - it quits.

2 bad entries
Ist entry - Bad Name - Try again
2nd entry - Bad name - Try again
3rd entry - Good Name - goes to it.
Tell it to quit - It searches again
Tell it to quit - It searches again
Tell it to quit - It quits

I am going to just be happy that it works with one bad entry and one good entry.
Thank you both so very much for helping me.

[johnske]

Hi ddh,

Before giving up, try this variation:

Option Explicit

Sub LineSearchTEST01(Optional SearchVal As String)
Dim MyValue$, MyFindNext&, Ans&
Dim FirstAddress$, Counter&
Dim sht As Worksheet, Cel As Range
Start:
If SearchVal = "" Then
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
If MyValue = "" Then
[C3].Select
Exit Sub
End If
Else
MyValue = SearchVal
End If
For Each sht In Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
"W", "X", "Y", "Z"))
With sht
sht.Activate
Set Cel = .Columns(3).Find(What:=MyValue)
Counter = 0
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
If MyFindNext = vbNo Then GoTo Finish
'Else it's vbYes & the search continues
Set Cel = .Columns(3).FindNext(Cel)
Loop Until Cel Is Nothing Or Cel.Address = FirstAddress
End If
End With
Next
If Counter = 0 Then
Sheets(1).Select [C3].Select
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
If Ans = vbYes Then
GoTo Start
Else
GoTo Finish
End If
End If
Exit Sub
Finish:
End Sub

Another (less preferred) alternative would be to replace all your Exit Sub's with End

John

[ddh]

Johnske - Thank you so very much for all of you time and knowledge.

The last code works perfect, I was going over your code line by and trying
to understand what you done in hopes that I can learn from your skills.
I have a very hard time with VBA, I struggle with it every day.
I have several books and CD's on learning VBA and it is very hard for me to understand the concepts.
Thank you again for all of your help.

[johnske]

Hi ddh,

Not a prob...

Just keep reading through the posts in the forum and KB, try them (and try to understand what they're doing (and maybe even how it could be done better)) you'll find that before long you'll be picking up on all the concepts and answering others questions for them

Regards,
John