in VBA How to place ifError in this VLookup formula?

[epardo87]

Hi there, I have the formula below working perfectly fine in a VBA Form, but when the result is not found it pops up an error and send me to debug.

Cells(emptyRow, 5).Value = WorksheetFunction.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:C"), 2, False)

I want to add an If isError condition so that if indeed Error then pops up a new window (CodigoUserForm.show)

Any suggestions?

Thanks!

 

[Domenic]

If you reference the Application object instead of the WorksheetFunction property, you'll get a non-breaking error when an error occurs. So you can test for an error as follows...

Dim vReturnVal As Variant

vReturnVal = Application.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:C"), 2, False)

If Not IsError(vReturnVal) Then
    Cells(emptyRow, 5).Value = vReturnVal
Else
    'Do something else
End If

Hope this helps!

 

[epardo87]

Thank you very much Domenic!

I placed it just as you wrote it and works perfectly, I also placed inside this If all other codes transferring data so that nothing is transferred if Error!

Best Regards!

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[epardo87]

Well It worked fine at first and I don't know why It began to send me errors, I tried to fix it, and sometimes worked when I pasted the code again, but then began to fail, this is the complete code of the button, If you could tell me what I'm doing wrong I'd really appreciate it:

Private Sub RegCommandButton_Click()

Sheet1.Unprotect Password:="2606"

Dim emptyRow As Long

Dim vReturnVal As Variant

'Make sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information

vReturnVal = Application.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:C"), 2, False)

If Not IsError(vReturnVal) Then
Cells(emptyRow, 5).Value = vReturnVal
Cells(emptyRow, 1).Value = Date
Cells(emptyRow, 2).Value = Time
Cells(emptyRow, 3).Value = SerieTextBox.Value
Cells(emptyRow, 4).Value = UserTextBox.Value
Cells(emptyRow, 6).Value = Application.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:D"), 3, False)
Cells(emptyRow, 7).Value = Application.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:E"), 4, False)
Cells(emptyRow, 8).Value = Application.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:F"), 5, False)
Else
CodeUserForm.Show

End If

ThisWorkbook.Save

Call UserForm_Initialize

End Sub

If you reference the Application object instead of the WorksheetFunction property, you'll get a non-breaking error when an error occurs. So you can test for an error as follows...

Dim vReturnVal As Variant

vReturnVal = Application.VLookup(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:C"), 2, False)

If Not IsError(vReturnVal) Then
    Cells(emptyRow, 5).Value = vReturnVal
Else
    'Do something else
End If

Hope this helps!

 

[Jonmo1]

If you're going to be returning multiple values based on the same lookup (just getting column 3 then 4 then 5)

Try This instead

vReturnVal = Application.Match(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:B"), 0)
 
If Not IsError(vReturnVal) Then
 Cells(emptyRow, 5).Value = Sheets("RecordPrint").Cells(vReturnVal, "C").Value
 Cells(emptyRow, 1).Value = Date
 Cells(emptyRow, 2).Value = Time
 Cells(emptyRow, 3).Value = SerieTextBox.Value
 Cells(emptyRow, 4).Value = UserTextBox.Value
 Cells(emptyRow, 6).Value = Sheets("RecordPrint").Cells(vReturnVal, "D").Value
 Cells(emptyRow, 7).Value = Sheets("RecordPrint").Cells(vReturnVal, "E").Value
 Cells(emptyRow, 8).Value = Sheets("RecordPrint").Cells(vReturnVal, "F").Value
 Else
 CodeUserForm.Show
 
End If

 

[epardo87]

Ok, So I made a few more tries and I found out that if a number is typed and not found it works! but when letters are typed then it fails and pops up an Error window, this leaves my codes vulnerable to people using it. It's not probably that they type letters but I wouldn't want to take that risk, is there any way to avoid this error when typing letters?

 

[epardo87]

If you're going to be returning multiple values based on the same lookup (just getting column 3 then 4 then 5)

Try This instead

vReturnVal = Application.Match(CLng(SerieTextBox.Value), Sheets("RecordPrint").Range("B:B"), 0)
 
If Not IsError(vReturnVal) Then
 Cells(emptyRow, 5).Value = Sheets("RecordPrint").Cells(vReturnVal, "C").Value
 Cells(emptyRow, 1).Value = Date
 Cells(emptyRow, 2).Value = Time
 Cells(emptyRow, 3).Value = SerieTextBox.Value
 Cells(emptyRow, 4).Value = UserTextBox.Value
 Cells(emptyRow, 6).Value = Sheets("RecordPrint").Cells(vReturnVal, "D").Value
 Cells(emptyRow, 7).Value = Sheets("RecordPrint").Cells(vReturnVal, "E").Value
 Cells(emptyRow, 8).Value = Sheets("RecordPrint").Cells(vReturnVal, "F").Value
 Else
 CodeUserForm.Show
 
End If

Thanks Jonmo1,

It did work and made it shorter once all other emptyrows where placed, I didn't know anything about VBA before Monday and I've learned a lot in these forums! either way I still having issue but only when typing letters instead of numbers in SerieTextBox.Value, any suggestion?

Best Regards!

 

[Jonmo1]

I still having issue but only when typing letters instead of numbers in SerieTextBox.Value, any suggestion?

Sure, don't type letters into the textbox.
Just kidding, lol.

You can test the textbox first to ensure it is a number.

If Isnumeric(SerieTextBox.Value) Then...

 

[epardo87]

Sure, don't type letters into the textbox.
Just kidding, lol.

You can test the textbox first to ensure it is a number.

If Isnumeric(SerieTextBox.Value) Then...

lol! Awesome, with that I've successfully finished the form!

Thanks a lot!