• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add message box to code

jassybun

Member
In this sub, is it possible to make a message box to user to let them know what was "Not found" so they can add any missing items and run again?

Sub Lookup()
Dim Rng As Range
Dim c As Range
Dim result As String
Set Rng = Range("c2:c" & Cells(Rows.Count, 2).End(xlUp).Row)

For Each c In Rng.Cells
On Error Resume Next
result = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Lookup.xlsm").Worksheets("Sheet1").Range("A2:B350"), 2, False)
If Err.Number <> 0 Then result = "Not Found"
On Error GoTo 0

c.Offset(, 3).Value = result
result = vbNullString


Next c
End Sub
 
Perhaps:
Code:
Sub Lookup()
Dim Rng As Range
Dim c As Range
Dim result As String
Set Rng = Range("c2:c" & Cells(Rows.Count, 2).End(xlUp).Row)

For Each c In Rng.Cells
  On Error Resume Next
  result = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Lookup.xlsm").Worksheets("Sheet1").Range("A2:B350"), 2, False)
  If Err.Number <> 0 Then
    result = "Not Found"
    MsgBox c.Value & " was not found"
  End If
  On Error GoTo 0
  c.Offset(, 3).Value = result
  result = vbNullString
Next c
End Sub
?
 
Or a single message at the end:
Code:
Sub Lookup()
Dim Rng As Range
Dim c As Range
Dim result As String
Dim NotFoundList()
NotFoundListCount = 1
ReDim NotFoundList(1 To 1)
Dim SomethingWasntFound As Boolean

Set Rng = Range("c2:c" & Cells(Rows.Count, 2).End(xlUp).Row)

For Each c In Rng.Cells
  On Error Resume Next
  result = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Lookup.xlsm").Worksheets("Sheet1").Range("A2:B350"), 2, False)
  If Err.Number <> 0 Then
    On Error GoTo 0
    result = "Not Found"
    ReDim Preserve NotFoundList(1 To NotFoundListCount)
    NotFoundList(NotFoundListCount) = c.Value
    NotFoundListCount = NotFoundListCount + 1
    SomethingWasntFound = True ' used later to decide whether to show a message or not.
  End If
  On Error GoTo 0
  c.Offset(, 3).Value = result
  result = vbNullString
Next c
If SomethingWasntFound Then
  MsgBox "Not found:" & vbLf & vbLf & Join(NotFoundList, vbLf)    'I hope the list isn't too long!
End If
End Sub
 
:):)thank you---the second option works because it isn't that many (just one or two, but we must catch) but thank you for doing both types as I will most likely use each of the codes again in different situations. thnx!!!!:):)
 
Back
Top