VBA vlookup not found msgbox

[PLwolves87]

Hi, i have a vlookup and would like a msgbox to appear if not found (rather than the textbox being left blank) below is my code if you could please help

Private Sub TextBox27_Change()
'VLookup procedure
Dim costperpallet As String
Dim myrange As Worksheet

costperpallet = Me.TextBox27.Value
Set myrange = ThisWorkbook.Worksheets("JONEN full")

On Error Resume Next
TextBox28.Value = Application.VLookup(TextBox27.Value, Sheets("JONEN full").Range("a:g"), 6, False)
On Error GoTo 0


End Sub

 

[rlv01]

Since you are using the Application.VLookup form you should be able to use IsError, so why not something like this:

    If IsError(Application.VLookup(TextBox27.Value, Sheets("JONEN full").Range("a:g"), 6, False)) Then
        TextBox28.Value = "Not found"
     else
        TextBox28.Value = Application.VLookup(TextBox27.Value, Sheets("JONEN full").Range("a:g"), 6, False)
    End If

(not tested)

(update: I had my syntax backwards!)

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Private Sub TextBox27_AfterUpdate()
    'VLookup procedure
    Dim costperpallet As String
    Dim MyLookUp    As Variant
    Dim wsJonenFull As Worksheet
    
    costperpallet = Me.TextBox27.Value
    If Len(costperpallet) = 0 Then Exit Sub
    
    Set wsJonenFull = ThisWorkbook.Worksheets("JONEN full")
    
    MyLookUp = Application.VLookup(costperpallet, wsJonenFull.Range("a:g"), 6, False)
    
    TextBox28.Value = IIf(Not IsError(MyLookUp), MyLookUp, "")

    If IsError(MyLookUp) Then MsgBox costperpallet & Chr(10) & "LookUp Value Not Found", 48, "Not Found"

End Sub

Dave

 

[PLwolves87]

Hi,
try this update to your code & see if does what you want

Private Sub TextBox27_AfterUpdate()
    'VLookup procedure
    Dim costperpallet As String
    Dim MyLookUp    As Variant
    Dim wsJonenFull As Worksheet
   
    costperpallet = Me.TextBox27.Value
    If Len(costperpallet) = 0 Then Exit Sub
   
    Set wsJonenFull = ThisWorkbook.Worksheets("JONEN full")
   
    MyLookUp = Application.VLookup(costperpallet, wsJonenFull.Range("a:g"), 6, False)
   
    TextBox28.Value = IIf(Not IsError(MyLookUp), MyLookUp, "")

    If IsError(MyLookUp) Then MsgBox costperpallet & Chr(10) & "LookUp Value Not Found", 48, "Not Found"

End Sub

Dave

Hi Dave,

no message box popped up when postcode wasnt found

 

[PLwolves87]

Hi,
try this update to your code & see if does what you want

Private Sub TextBox27_AfterUpdate()
    'VLookup procedure
    Dim costperpallet As String
    Dim MyLookUp    As Variant
    Dim wsJonenFull As Worksheet
   
    costperpallet = Me.TextBox27.Value
    If Len(costperpallet) = 0 Then Exit Sub
   
    Set wsJonenFull = ThisWorkbook.Worksheets("JONEN full")
   
    MyLookUp = Application.VLookup(costperpallet, wsJonenFull.Range("a:g"), 6, False)
   
    TextBox28.Value = IIf(Not IsError(MyLookUp), MyLookUp, "")

    If IsError(MyLookUp) Then MsgBox costperpallet & Chr(10) & "LookUp Value Not Found", 48, "Not Found"

End Sub

Dave

Hi Dave,

i got this to work, instead of AFTERUPDATE() i selected CHANGE() and then it works.

thank you