on error loop

**manforeyes** · 01-10-2009, 12:21 PM

Hi all,

I have a search button that do a vlookup based on what option button is choosen. for a couple of weeks I have been trying to write a error handle but with no success. I want to write a few different handles but it doesn't work. here is my code. First handler I want to create is if not found please update the outlook list If not found again maybe a typo if not found choose another option than loop. I hope i explained this correctly..

Private Sub cmdsearch_Click()
Dim a As String
Dim b As String
Dim c As String
Dim answer As String
Dim answer2 As String


          

'Continue:
'On Error GoTo trap
'On Error GoTo trap2
'If obtclient.Value = False And optalias.Value = False Then
 ' MsgBox "Select an option to search for client", vbOKCancel'

If obtclient.Value = True Then
a = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("a1:C10000"), 2, False)
txtCname = txtsearch
txtAlais = a
txtphone = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("a1:c10000"), 3, False)
ElseIf optalias.Value = True Then
b = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("D1:F10000"), 3, False)
txtAlais.Value = txtsearch
txtCname = b
txtphone = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("D1:F10000"), 2, False)

Else
optphone.Value = True
c = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("g1:I10000"), 2, False)

 
txtphone = txtsearch
txtCname = c
txtAlais = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("g1:I10000"), 3, False)

End If




 lblstartup.Visible = False
 lblstartup2.Visible = False
 
 lbldate.Visible = True
txtdate.Visible = True
'Frame3.Visible = False
Frame2.Visible = True
Frame5.Visible = True
lblissue.Visible = True
txtissue.Visible = True
cmdsi.Visible = True
lbltitle.Visible = True
txttitle.Visible = True
lblsystem.Visible = True
txtsystem.Visible = True
lblcomponent.Visible = True
txtcomp.Visible = True
lblitem.Visible = True
txtitem.Visible = True
Label4.Visible = True
txtassign.Visible = True
lbldescription.Visible = True
txtdescription.Visible = True
Label5.Visible = True
txtsolution.Visible = True
 
 ' Resume Continue
  


'answer = MsgBox("yes", vbQuestion + vbOKCancel, "Error")
'If answer = vbOK Then Resume Continue

'trap2:
'answer2 = MsgBox("no", vbQuestion + vbOKCancel, "Error")
'If answer2 = vbOK Then Resume Continue


'MsgBox "Select an option to search for client", vbOKCancel






End Sub

**royUK** · 01-10-2009, 12:38 PM

Why don't you use the VBA .Find method?

**manforeyes** · 01-10-2009, 12:39 PM

how can you help ?

**royUK** · 01-10-2009, 12:52 PM

I'm not sure what you are doing.

**manforeyes** · 01-10-2009, 12:55 PM

o.k I inport info from my work info so I can lookup easier and put it on a certain worksheet now based on that work sheet it will perform a vlookup based on what I select

**royUK** · 01-10-2009, 01:13 PM

Checking for errors with .Find is much easier, something like this

Dim cl     As Range
    If obtclient.Value = True Then

        Set cl = Worksheets("Outlook Info").Range("b1:b10000").Find(Worksheets("INPUT").Range("B1").Value, LookIn:=xlValues)
        If cl Is Nothing Then
            MsgBox "Not there"
            'do something else
        Else: a = cl.Value
            txtCname = txtsearch
            txtAlais = a
        End If
        'etc

**manforeyes** · 01-10-2009, 01:16 PM

I have three criteria what do you mean do something else

**manforeyes** · 01-10-2009, 01:23 PM

what is LookIn:=xlValues)

**royUK** · 01-10-2009, 01:28 PM

take a look at the VBA helpfiles for .Find

Do something else means exactly that, add code for whatver you want to do if the search is unsuccessful

**manforeyes** · 01-10-2009, 01:31 PM

cool like if nothing found go to 2 right ?

**royUK** · 01-10-2009, 02:08 PM

Somethin like that

**manforeyes** · 01-12-2009, 08:45 AM

Hi again I tried the find however doesn't seem to work this is my latest attempt at error handler

Private Sub cmdsearch_Click()
Dim a As String
Dim b As String
Dim c As String
Dim answer As String
Dim answer2 As String


    On Error GoTo ErrorHandler
Top:
 
 


If obtclient.Value = True Then
a = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("a1:C10000"), 2, False)


txtCname = txtsearch
txtAlais = a
txtphone = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("a1:c10000"), 3, False)
ElseIf optalias.Value = True Then
b = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("D1:F10000"), 3, False)
txtAlais.Value = txtsearch
txtCname = b
txtphone = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("D1:F10000"), 2, False)

Else
optphone.Value = True
c = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("g1:I10000"), 2, False)

 
txtphone = txtsearch
txtCname = c
txtAlais = Application.WorksheetFunction.VLookup(Worksheets("INPUT").Range("B1"), Worksheets("Outlook Info").Range("g1:I10000"), 3, False)

End If
ErrorHandler:



MsgBox "Nothing found"

GoTo Top


 'End If
 lblstartup.Visible = False
 lblstartup2.Visible = False
 
 lbldate.Visible = True
txtdate.Visible = True
'Frame3.Visible = False
Frame2.Visible = True
Frame5.Visible = True
lblissue.Visible = True
txtissue.Visible = True
cmdsi.Visible = True
lbltitle.Visible = True
txttitle.Visible = True
lblsystem.Visible = True
txtsystem.Visible = True
lblcomponent.Visible = True
txtcomp.Visible = True
lblitem.Visible = True
txtitem.Visible = True
Label4.Visible = True
txtassign.Visible = True
lbldescription.Visible = True
txtdescription.Visible = True
Label5.Visible = True
txtsolution.Visible = True
 


Resume Next








End Sub

**manforeyes** · 01-12-2009, 10:05 AM

what I am trying to do is prevent a run-time error to happenif the vlookup doesn't find what the user is searching for

**royUK** · 01-12-2009, 10:17 AM

Did you try replacing all the VLOOKUPS with.Find?

**manforeyes** · 01-12-2009, 10:17 AM

yes but no luck

**manforeyes** · 01-12-2009, 10:24 AM

how would I return the value from the next row example someone searches for Pete Peterson than there alais will appear and so will there extension ?

**royUK** · 01-12-2009, 10:31 AM

Dim cl     As Range
    If obtclient.Value = True Then

        Set cl = Worksheets("Outlook Info").Range("b1:b10000").Find(Worksheets("INPUT").Range("B1").Value, LookIn:=xlValues)
        If cl Is Nothing Then
            MsgBox "Not there"
            'do something else
        Else: a = cl.Value
           b=cl.offset(1,0).Value
            txtCname = txtsearch
            txtAlais = a
        End If
        'etc

You probably need to attach the workbook.