VBA Vlookup - if part number not found then display Msgbox and Exit Sub

[madi1004]

Hello,

I have following macro which on Error will resume to next.
Now I want to display a message box if an error is found and after exit SUB
Message box( "Part number" & "PN" & "not found. Please define packaging details")
By PN should be displayed the part number that is not found.

Sub Vlookup()

Dim Volume As Worksheet
Dim Packaging As Worksheet
Dim PN As Long
Dim Pcs As Long
Dim x As Variant
Dim dataRNG As Range

Set Volume = ThisWorkbook.Worksheets("Volume per shipment")
Set Packaging = ThisWorkbook.Worksheets("Packaging details")

PN = Volume.Range("A" & Rows.Count).End(xlUp).Row
Pcs = Packaging.Range("A" & Rows.Count).End(xlUp).Row

Set dataRNG = Packaging.Range("A2:G" & Pcs)

For x = 2 To PN

On Error Resume Next

Volume.Range("D" & x).Value = Application.WorksheetFunction.Vlookup( _
                            Volume.Range("A" & x).Value, dataRNG, 7, 0)

Next x

End Sub

Thank you!
Madi

 

[Fluff]

Do you want one message box to show all missing part numbers, once the loop has finished?

 

[madi1004]

if possible yes, if not is ok to show only one

 

[Fluff]

Ok, how about

Sub Vlookup()

Dim Volume As Worksheet
Dim Packaging As Worksheet
Dim PN As Long
Dim Pcs As Long
Dim x As Variant
Dim dataRNG As Range
Dim Msg As String
Dim PartNo As Variant

Set Volume = ThisWorkbook.Worksheets("Volume per shipment")
Set Packaging = ThisWorkbook.Worksheets("Packaging details")

PN = Volume.Range("A" & Rows.Count).End(xlUp).row
Pcs = Packaging.Range("A" & Rows.Count).End(xlUp).row

Set dataRNG = Packaging.Range("A2:G" & Pcs)

For x = 2 To PN


   PartNo = Application.Vlookup(Volume.Range("A" & x).Value, dataRNG, 7, 0)
   If IsError(PartNo) Then
      Msg = Msg & vbLf & Volume.Range("A" & x).Value
   Else
   Volume.Range("D" & x).Value = PartNo
Next x
If Msg <> "" Then MsgBox "Part number" & Msg & vbLf & "not found. Please define packaging details"
End Sub

 

[madi1004]

Thank you!

I get an compile error : Next without For

Where should I put "Next x" ?

 

[Fluff]

Oops, forgot the End If

Sub Vlookup()

Dim Volume As Worksheet
Dim Packaging As Worksheet
Dim PN As Long
Dim Pcs As Long
Dim x As Variant
Dim dataRNG As Range
Dim Msg As String
Dim PartNo As Variant

Set Volume = ThisWorkbook.Worksheets("Volume per shipment")
Set Packaging = ThisWorkbook.Worksheets("Packaging details")

PN = Volume.Range("A" & Rows.Count).End(xlUp).row
Pcs = Packaging.Range("A" & Rows.Count).End(xlUp).row

Set dataRNG = Packaging.Range("A2:G" & Pcs)

For x = 2 To PN


   PartNo = Application.Vlookup(Volume.Range("A" & x).Value, dataRNG, 7, 0)
   If IsError(PartNo) Then
      Msg = Msg & vbLf & Volume.Range("A" & x).Value
   Else
      Volume.Range("D" & x).Value = PartNo
   End If
Next x
If Msg <> "" Then MsgBox "Part number" & Msg & vbLf & "not found. Please define packaging details"
End Sub

 

[madi1004]

End If was missing

 

[madi1004]

Thank you ..it works now

 

[Fluff]

You're welcome & thanks for the feedback.