|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
January 29th, 2005, 07:51 PM
|
|||
|
|||
|
Loop Question?
I am trying to make this code work and I need some help please.
Here is my problem - I can not get the err Trap loop to work? If I enter a name in the Input box that is not in the database it will loop and ask me again, But If I enter another wrong name it will error out. I am trying to get it to keep looking (Looping) untill it finds a name in the data base or I tell it to quit looking. I don't have the i = i and Do and i = i + 1 and loop in the correct place and I am not that good at VBA and I am trying to learn it. Thank you for your help. Code:
Sub LineSearch()
Dim MyValue, MyFindNext
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
If MyValue = "" Then
[C3].Select
Exit Sub
End If
On Error GoTo err_Trap
Worksheets("ORGINAL ").Columns(3).Find(What:=MyValue).Activate
MyFindNext = vbYes
Do Until MyFindNext <> vbYes
MyFindNext = MsgBox("Next " & MyValue & "?", _
vbYesNo, "Find Next")
If MyFindNext = vbNo Then
Exit Sub
End If
Worksheets("ORGINAL ").Columns(3).FindNext(after:=ActiveCell).Activate
Loop
Exit Sub
err_Trap:
i = 1
If Err.Number = 91 Then
MsgBox "Could not find " & MyValue & " anywhere on this sheet.", , "Unsuccessful search"
End If
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
Do
i = i + 1
Loop
If MyValue = "" Then
[C3].Select
Exit Sub
End If
Worksheets("ORGINAL ").Columns(3).Find(What:=MyValue).Activate
MyFindNext = vbYes
Do Until MyFindNext <> vbYes
MyFindNext = MsgBox("Next " & MyValue & "?", _
vbYesNo, "Find Next")
If MyFindNext = "" Then
Exit Sub
End If
If MyFindNext = vbNo Then
Exit Sub
End If
Worksheets("ORGINAL ").Columns(3).FindNext(after:=ActiveCell).Activate
Loop
End Sub
Last edited by Shadow Wizard : January 30th, 2005 at 06:58 AM. Reason: added code tags - please do it yourself with [code] and [/code] tags next.
|
|
#2
January 29th, 2005, 09:18 PM
|
|||
|
|||
|
Can't you just step through your code with the debugger and find out where it fails?
__________________
====== Doug G ====== I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain
|
|
#3
January 30th, 2005, 06:32 AM
|
|||
|
|||
|
Quote:
Worksheets("ORGINAL ").Columns(3).Find(What:=MyValue).Activate It always stops at this line, but since I still don't know what to do. I have put the code in so many different places trying to solve the problem but nothing has worked. That is why I thought I would ask for some help. Thank you Last edited by Shadow Wizard : January 30th, 2005 at 07:01 AM. Reason: removed the duplicate part.
|
|
#4
January 30th, 2005, 07:37 PM
|
|||
|
|||
|
When you say "errors out" what is the error message?
I don't do much Excel VBA coding (well, none, actually  ) but perhaps the error message will give a clue.
|
|
#5
January 30th, 2005, 08:44 PM
|
|||
|
|||
|
Thank you for your help.
Here is the message. Run-Time Error 91 Object Variable or with block variable not set. It should be able to loop in the Err-Trap, but I can not get it to. Thank you.
|
|
#6
January 30th, 2005, 10:10 PM
|
|||
|
|||
|
Do
i = i + 1 Loop This code in your err_trap routine makes no sense to me. Also review the flow of your program code. I don't quite understand how you ever get to the code at the bottom after the err_trap ends.
|
|
#7
January 31st, 2005, 11:28 AM
|
|||
|
|||
|
That has to be a error made by me.
Since I am not very good with VBA, I am not surprised that I put something in there that does not make any sense. See if you can copy my code into a Excel sheet (rename sheet 1 to Orginal and put the data in column C) and put something in the cells for it to search (Like A B C in three different cells) for and then you can see it work when you type in something that is in the sheet, then type in something that is not on the sheet and do this twice and you will see the error that I told you about. I got the code i = 1 Do i = i + 1 Loop from a Excel Marco book under The Do Loop section. This was my dumb attempt at trying to do it on my own, I keep trying to learn.
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Loop Question? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
