Not Sure What To Do

NeedsHelp2

New Member
Joined
Nov 20, 2005
Messages
22
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
At its simplest:

Code:
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?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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

New Member
Joined
Nov 20, 2005
Messages
22
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Same principle:

Code:
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

New Member
Joined
Nov 20, 2005
Messages
22
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

Code:
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

New Member
Joined
Nov 20, 2005
Messages
22
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Silly me. The second If can't be True if the first one is. Try:

Code:
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
 

Forum statistics

Threads
1,078,537
Messages
5,341,030
Members
399,412
Latest member
Bryanhj

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top