Function to track count is not working 1

[alwayslrN]

I am using Excel 2002

I have a macro that uses I/O (found it on the web) to read through files in a directory and runs variouse sub routines for each file. Within one routine I track the number of files processed, in another I attempt to track the number of those files that have errors (#N/A, #VALUE, etc. in the worksheet) and withing the routine that tracks the number of files processed to display the results of total processed, total successful, and total errors. The total successful and total errors is not correct and I am not sure what I am doing wrong.

Routine that tracks number of files processed and displays the results

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim processCnt As Long
Dim msg As String
Dim ErrorsU As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim RemitFileName As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

processCnt = 0

Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\Remittance\Prep\062007"
        .FileType = msoFileTypeAllFiles 'msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"
        
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 
                 Set RemitFileName = wbResults
                 LoadName = RemitFileName.Name
                                  
                 'DO YOUR CODE HERE
                 Call SelectMacro
                 Call SelectRemitRange
                 
                 wbResults.Close SaveChanges:=False
                 
                 processCnt = (lCount + 1) - 1

                 Next lCount
            End If
    End With
    
    ErrorsU = NumErrors(ErrCnt)
     msg = "Total files processed: " & vbTab & processCnt
     msg = msg & vbCrLf & "Processed successfully: " & vbTab & processCnt - ErrorsU
     msg = msg & vbCrLf & "Number of errors: " & vbTab & vbTab & ErrorsU
     MsgBox msg
    
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Routine that contains the tracking of number of errors

Sub SelectRemitRange()
    Dim RemitLastRow As Long
    Dim RemitLast As Integer
    Dim ErrCnt As Long
    
    Application.ScreenUpdating = False
    
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    
    RemitLastRow = Cells(Cells.Rows.Count, "b").End(xlUp).Row
    RemitLast = RemitLastRow
    Range("A1" & ":X" & RemitLast).Select
    
   
    If ActiveCell.Errors.Item(xlEvaluateToError).Value = True Then
        'MsgBox "you got a boo boo"
        ErrCnt = 1
        Call NumErrors(ErrCnt)
        'copy the selected area
        Call CopyMoveErrors
    Else
        Selection.Copy
        Call ExportToTextFile
        Call SaveRemittance
    End If
    
    Application.ScreenUpdating = True

End Sub

Function used above

Static Function NumErrors(ErrCnt) As Long
    Dim NumErr As Long
    
    NumErrors = 0
    NumErrors = (ErrCnt + 1) - 1
    
End Function

Thanks in advance

 

[PHV]

Tip: Use the Option Explicit expression.
Study the difference between a Sub and a Function and how to call them.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Not sure why I did not already have Option Explicit on, but I was able to find a declaration issue in RunCodeOnAllXLSFiles.

Now I am trying to workout the proper use of functions so I can fix the issue.

Thanks for the tip.

 

[alwayslrN]

Thanks PH!!!

I was able to get it to working after making a few tweaks AND found a couple other variables that were declared but my copy and pasting was missing letters causing it to look like they were not declared.

 

[WalkerEvans]

alwayslrn

Tip: Use the [COLOR=red yellow]Star[/color] link at the bottom of the post when you want to thank someone. It not only counts towards TipMaster of the Week, it also helps others know that there is valuable information in the post that they might find useful.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long