Not Sure What To Do

[NeedsHelp2]

I'm using the code below which works fine if the user selects an item in the combo box. The problem is if they mistype something, it stops the whole procedure. I know the solution is something to do with trapping errors but I don't know how set it up.


Label35.Caption = Application.VLookup(cmbN.Value, Range("SiteName"), 2, False)

 

[Andrew Poulsom]

At its simplest:

On Error Resume Next
Label35.Caption = Application.VLookup(cmbN.Value, Range("SiteName"), 2, False)
On Error GoTo 0

Do you want to take any action if there is an error?

 

[NeedsHelp2]

If there is an error then the label to read "Warning"

 

[Andrew Poulsom]

Try:

On Error Resume Next 
Label35.Caption = Application.VLookup(cmbN.Value, Range("SiteName"), 2, False) 
If Err <> 0 Then
   Err.Clear
   Label35.Caption = "Warning"
End If
On Error GoTo 0

 

[NeedsHelp2]

Still struggling to resolve the error trapping so am seeking further guidence. Currently if correct values are selected then the code below works fine. My problem is that every now and again the combination of cmbG & cmbL cannot always be found in the table called "finance". Is there away in which a default value can be entered if there is no match?

If txtQ.Text = "Y" Or txtR.Text = "Y" Then

Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 2, False)

End If

 

[Andrew Poulsom]

Same principle:

If txtQ.Text = "Y" Or txtR.Text = "Y" Then 
   On Error Resume Next
   Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 2, False) 
   If Err <> 0 Then
      Err.Clear
      Label37.Caption = "Not Found"
   End If
   On Error Goto 0
End If

 

[NeedsHelp2]

Sorry about this but am still having problems. Thought I could follow on using your example but am unable to get the next part to work.

This is what I'm currently using:

If txtQ.Text = "Y" Or txtR.Text = "Y" Or txtS.Text = "Y" Or txtT.Text = "Y" Or txtU.Text = "Y" Then

On Error Resume Next
Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 2, False)
If Err <> 0 Then
Err.Clear
Label37.Caption = "Not Found"
End If
On Error GoTo 0
End If

I need to incorporate this second part so that if the first if statement is not found, then the second is looked for and if that can't be found to then put in the caption not found.

If txtV.Text = "Y" Or txtW.Text = "" And txtQ.Text = "" And txtR.Text = "" And txtS.Text = "" And txtT.Text = "" And txtU.Text = "" Then

Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 4, False)


End If

Hope you can assist further.

 

[Andrew Poulsom]

Like this?

If txtQ.Text = "Y" Or txtR.Text = "Y" Or txtS.Text = "Y" Or txtT.Text = "Y" Or txtU.Text = "Y" Then 
   On Error Resume Next 
   Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 2, False) 
   If Err <> 0 Then 
      Err.Clear 
      If txtV.Text = "Y" Or txtW.Text = "" And txtQ.Text = "" And txtR.Text = "" And txtS.Text = "" And txtT.Text = "" And txtU.Text = "" Then 
         Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 4, False) 
         If Err <> 0 Then 
            Err.Clear 
            Label37.Caption = "Not Found" 
         End If 
      End If
   End If
   On Error GoTo 0 
End If

 

[NeedsHelp2]

Andrew, it's almost there. When it can't find one of the two criteria its skipping the final part and not putting in the "Not Found". Have tried amending your code but can't get it right.

 

[Andrew Poulsom]

Silly me. The second If can't be True if the first one is. Try:

If txtQ.Text = "Y" Or txtR.Text = "Y" Or txtS.Text = "Y" Or txtT.Text = "Y" Or txtU.Text = "Y" Then 
   On Error Resume Next 
   Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 2, False) 
   If Err <> 0 Then 
      Err.Clear 
   End If 
ElseIf txtV.Text = "Y" Or txtW.Text = "" And txtQ.Text = "" And txtR.Text = "" And txtS.Text = "" And txtT.Text = "" And txtU.Text = "" Then 
   Label37.Caption = Application.VLookup(cmbG.Value & cmbL.Value, Range("finance"), 4, False) 
   If Err <> 0 Then 
      Err.Clear 
      Label37.Caption = "Not Found" 
   End If 
End If 
On Error GoTo 0