My code doesnt advise which file is missing when Msgbox is shown

[ipbr21054]

Hi,
I am using the code below but one part doesnt work correctly & i am unable to see the reason why.

I run the code & i then see a Msgbox pop up saying "PDF DOES NOT EXIST"
I am not told which PDF its refering to,can you see an issue in the code which would cause this.

Obviously it should say something like PDF 123 DOES NOT EXIST

Thanks

Private Sub HyperlinkAllInvoiceNumbers_Click()
    Dim lastRow As Long, myPath As String, fileName As String, fName As Variant
    myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
    lastRow = Range("P" & Rows.Count).End(xlUp).Row
    For i = 6 To lastRow
        If Range("P" & i) <> "N/A" Then
            If Not Dir(myPath & Range("P" & i) & ".pdf", vbDirectory) = vbNullString Then
                ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & Range("P" & i) & ".pdf"
            Else
                MsgBox "PDF " & Range("P" & i) & " DOES NOT EXIST.", vbExclamation, "PDF IS MISSING IN THE FOLDER"
            End If
        End If
    Next i
    MsgBox "ALL HYPERLINKS ARE NOW COMPLETE.", vbInformation, "HYPERLINKING THE INVOICE NUMBER"
End Sub

 

[DanteAmor]

Try this:

Private Sub HyperlinkAllInvoiceNumbers_Click()
  Dim lastRow As Long, myPath As String, fileName As String, fName As Variant
  Dim i As Long
  myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
  lastRow = Range("P" & Rows.Count).End(xlUp).Row
  For i = 6 To lastRow
    fileName = Range("P" & i).Value
    If fileName <> "N/A" Then
      If Not Dir(myPath & fileName & ".pdf", vbDirectory) = vbNullString Then
        ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & fileName & ".pdf"
      Else
        If fileName = "" Then fileName = "The cell is empty"
        MsgBox "PDF " & fileName & " DOES NOT EXIST.", vbExclamation, "PDF IS MISSING IN THE FOLDER"
      End If
    End If
  Next i
  MsgBox "ALL HYPERLINKS ARE NOW COMPLETE.", vbInformation, "HYPERLINKING THE INVOICE NUMBER"
End Sub

 

[ipbr21054]

Hi,
That works better BUT i see this message PDF The cell is empty Does not exist.

When the message is shown can we have it also show the row number please.

Ive looked down the list & dont see an empty cell.
This would track it down should my eyes have failed me.

Thanks

 

[DanteAmor]

Try:

Private Sub HyperlinkAllInvoiceNumbers_Click()
  Dim lastRow As Long, myPath As String, fileName As String, fName As Variant
  Dim i As Long
  myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
  lastRow = Range("P" & Rows.Count).End(xlUp).Row
  For i = 6 To lastRow
    fileName = Range("P" & i).Value
    If fileName <> "N/A" Then
      If Not Dir(myPath & fileName & ".pdf", vbDirectory) = vbNullString Then
        ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & fileName & ".pdf"
      Else
        If fileName = "" Then
          MsgBox "The cell is empty in the row : " & i
        Else
          MsgBox "PDF " & fileName & " DOES NOT EXIST. Row: " & i, vbExclamation, "PDF IS MISSING IN THE FOLDER"
        End If
      End If
    End If
  Next i
  MsgBox "ALL HYPERLINKS ARE NOW COMPLETE.", vbInformation, "HYPERLINKING THE INVOICE NUMBER"
End Sub

 

[ipbr21054]

Many thanks now sorted