How to identify code line generating error value as #N/A

[ThrottleWorks]

Hi,

I am trying to amend a macro which is written by someone else.
This macro process excel data and creates an e-mail.

For some of the fields the macro generates values as #N/A.
I tried running the code by doing F8. But there are multiple functions used in the code.

Even if keep running the macro in F8 mode, I am not able understand at which exact step value is generated as #N/A.

I guess there is some technique to locate this type of errors.
Can anyone please help me this. Kindly guide how I should identify the error.

 

[ThrottleWorks]

Please find below the code for your reference (if required).

'Main Module
'Edited by aaa on 18-11-2015

Option Explicit
Public strAttachment As String
Public StrWarnings As String

Sub CreateReport()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Dim arrList As Variant
    Dim WBOutput As Workbook 'This in the newly added workbook
    Dim shtAPAC As Worksheet 'This is Sheet1 of the newly added workbook
    Dim shtEU As Worksheet 'Add another Worksheet in newly added Workbook
    Dim shtNA As Worksheet 'Add another Worksheet in newly added Workbook
    Dim strSubjectLine As String 'Not used anywhere
 
    'Clear contents from Main Sheet of the macro
    Range("F1:J1").ClearContents
 
    'Check for row data
    'This is the Last Row of Main sheet - 6 starting rows.
    If shtMain.Range("A17").CurrentRegion.Rows.Count < 2 Then: MsgBox "Raw data is missing!", vbInformation, "Missing Data": Exit Sub
    If OutlookExists = False Then Exit Sub
 
    'Working with RowData
    With shtRawData
        If .AutoFilterMode = True Then .Cells.AutoFilter
        'Mapping Sheet of Macro gets activate
        .Activate
        .Cells.Clear
        ActiveWindow.DisplayFormulas = False
        arrList = Array("Trans Ccy", "Debit Account", "Credit Account", "Trans Amt", "Initiator", "Initiator CC", "Debit Account Description", "Debit Account Type", "Debit Account Stream", "Credit Account Description", "Credit Account Type", "Credit Account Stream", "Effective Date", "Event Status", "Trans Type", "Jrnl Description", "Qty", "Cusip", "Cusip Description", "Event Entry Time (GMT)", "Eligible Authorizers", "Authorization Category", "Authorization Categories", "Comments", "Event OID", "Mnemonic", "Jrnl Code", "Order Number", "Base Amt", "Base Ccy", "Reason Code", "Approver", "A/M Indicator", "FAC", "Authorization Time (GMT)", "Authorization Comment", "Event VID", "Event RID", "Action OID", "Action VID", "Action RID")
        If IsColomunMissing(arrList, shtMain, shtMain.Range("7:7")) = True Then Exit Sub
        .Range("A1:AO1").Value = arrList
        shtMain.Range("A17").CurrentRegion.AdvancedFilter xlFilterCopy, "", .Range("A1").CurrentRegion
    End With
 
    'Creating Output File
    Set WBOutput = Workbooks.Add
    Set shtAPAC = WBOutput.Sheets(1)
    Set shtEU = WBOutput.Worksheets.Add
    Set shtNA = WBOutput.Worksheets.Add
 
    shtAPAC.Name = "APAC"
    shtEU.Name = "EU"
    shtNA.Name = "NA"
 
    shtPrepareFinalTable.Cells.Clear
 
    FilterDataByRegion WBOutput, shtAPAC
    FilterDataByRegion WBOutput, shtEU
    FilterDataByRegion WBOutput, shtNA
 
    shtMain.Activate
    WBOutput.SaveAs ThisWorkbook.Path & Application.PathSeparator & "Wash Accounts report " & Format(Date, "dd.mm.yyyy") & ".xlsx"
    strAttachment = WBOutput.FullName
 
    WBOutput.Close False
 
    'Warnigns
    shtMain.Activate
    shtMain.Range("F1").Value = StrWarnings
    'MsgBox "Mail is Drafted." & vbCrLf & vbCrLf & "Warnigs :" & vbCrLf & StrWarnings
    StrWarnings = ""
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Function IsFileOpen(FileName As String)
    Dim filenum As Integer, errnum As Integer
  
    On Error Resume Next  ' Turn error checking off.
    filenum = FreeFile()  ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open FileName For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err          ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
        IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True
        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Function GetFilenameFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function


Function IsColomunMissing(arrList As Variant, sht As Worksheet, RngHeader As Range) As Boolean
   
    Dim I As Long
    Dim foundCell As Range, OriginalPosition As Range
    Dim TempRange As Range
    Dim TempWorkbook As Workbook
    Dim TempSheet As Worksheet
   
    IsColomunMissing = False
    For I = 0 To UBound(arrList)
            'Main Sheet select
            sht.Select
            Set foundCell = RngHeader.Find(arrList(I), RngHeader.Resize(1, 1), , xlWhole)
            'Checking if all the headers are present in Main Sheet
            If foundCell Is Nothing Then
                MsgBox "Column '" & arrList(I) & "' is not found in sheet '" & sht.Name & "' " & vbNewLine & "Exiting !", vbInformation, "Column missing"
                IsColomunMissing = True
                Exit Function
            End If
    Next I
End Function