Code:
Dim wb As Workbook
Dim ws As Worksheet
Dim TotalTrans As Integer
Dim FoundRow As Range
Dim MyFolder, MyFile, MyFileName, MySheetName, ReportDate As String
Dim FileType As String
Dim MisFileName() As String, PaymentFileType() As String
Dim MisTrans() As Integer
Dim i, j, k, m As Integer
i = 2
j = 2
k = 1
m = 1
MyFolder = "D:\TotalCompare"
MyFile = Dir(MyFolder & "\*.txt")
Do While MyFile <> ""
Workbooks.OpenText Filename:=MyFolder & "\" & MyFile, StartRow:=1, DataType:=xlDelimited, Comma:=True, Tab:=False, Semicolon:=False, Space:=False, Other:=False
If Left(ActiveWorkbook.Name, 21) = "ReverseFile" Or Right(ActiveWorkbook.Name, 12) = "RES.txt" Then
If Left(ActiveWorkbook.Name, 21) = "ReverseFile" Then
MySheetName = Replace(ActiveWorkbook.Name, "txt", "")
ReportDate = Right(Replace(MySheetName, ".", ""), 8)
MyFileName = Replace((Replace(MySheetName, "_" & ReportDate & ".", "")), "ReverseFile", "")
FileType = "M.I.S."
TotalTrans = ActiveSheet.UsedRange.Rows.Count
Else
MyFileName = Replace(ActiveWorkbook.Name, ".RES.txt", "")
FileType = "M.I.S."
TotalTrans = ActiveSheet.UsedRange.Rows.Count
End If
ThisWorkbook.Worksheets(2).Activate
ActiveSheet.Cells(i, 1).Value = i - 1
Worksheets(2).Cells(i, 2).Value = MyFileName
Worksheets(2).Cells(i, 3).Value = FileType
Worksheets(2).Cells(i, 4).Value = TotalTrans
i = i + 1
Else
MyFileName = Replace(ActiveWorkbook.Name, ".txt", "")
TotalTrans = ActiveSheet.UsedRange.Rows.Count
ThisWorkbook.Worksheets(1).Activate
Worksheets(1).Cells(j, 1).Value = j - 1
Worksheets(1).Cells(j, 2).Value = MyFileName
Worksheets(1).Cells(j, 3).Value = "Source File"
Worksheets(1).Cells(j, 4).Value = TotalTrans
Worksheets(1).Cells(j, 6).Formula = ""="B" & j - "B" & j""
'Here is the problem to insert formula
j = j + 1
End If
For Each wb In Workbooks
If Not wb.Name = ThisWorkbook.Name Then
wb.Close SaveChanges:=False
End If
Next wb
MyFile = Dir
Loop
ThisWorkbook.Worksheets(2).Activate
ReDim MisFileName(1 To i - 2) As String
ReDim PaymentFileType(1 To i - 2) As String
ReDim MisTrans(1 To i - 2) As Integer
For k = 1 To i - 2 Step 1
MisFileName(k) = ActiveSheet.Cells(k + 1, 2)
PaymentFileType(k) = ActiveSheet.Cells(k + 1, 3)
MisTrans(k) = ActiveSheet.Cells(k + 1, 4)
Next k
ThisWorkbook.Worksheets(1).Activate
For m = 1 To i - 2 Step 1
Set FoundRow = Worksheets(1).Range("B:B").Find(What:=MisFileName(m), SearchDirection:=xlNext, LookIn:=xlValues, lookat:=xlWhole)
If FoundRow Is Nothing Then
Worksheets(1).Cells(j, 1).Value = j
Worksheets(1).Cells(j, 2).Value = MisFileName(m)
Worksheets(1).Cells(j, 3).Value = PaymentFileType(m)
Worksheets(1).Cells(j, 5).Value = MisTrans(m)
Worksheets(1).Cells(j, 6).Formula = "="B" & j - "B" & j"
j = j + 1
Else
ActiveSheet.Cells(FoundRow.Row, 5).Value = MisTrans(m)
End If
Next m
Worksheets(1).Cells(j, 3) = "Total="
Worksheets(1).Cells(j, 4) = Application.WorksheetFunction.Sum(Range("D2:D" & j))
Worksheets(1).Cells(j, 5) = Application.WorksheetFunction.Sum(Range("E2:E" & j))
Worksheets(1).Cells(j, 6) = Application.WorksheetFunction.Sum(Range("F2:F" & j))
