Insert Formula in a cell

[haribole]

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))

 

[RoryA]

How about giving us a clue what you want? Subtracting the value in column B from the value in column B seems fairly pointless to me.

 

[haribole]

Sorry it would be:
Worksheets(1).Cells(j, 6).Formula = "="B" & j - "C" & j"

Actually I want to subtract column C from Column B and insert the formula in Column D

Please help !!

 

[RoryA]

Column D is column 4, not 6.

Worksheets(1).Cells(j, "D").FormulaR1C1 = "=RC2-RC3"

would be the simplest way.

 

[haribole]

Thank you for your valuable solution !!

Is there any way to avoid "R1C1 Reference Style" ?

Please !!

 

[RoryA]

Yes, though I can't see any benefit:

Worksheets(1).Cells(j, "D").Formula = "=B" & j & "-C" & j

 

[haribole]

Thanks !!! it it working