Using Formular1c1

Question

Hi, I’d like to ask you a question. My code is responsible for reading a column, if the value found is different from the previous one, it adds a row, paints a specific color and in the reference column writes "subtotal". My last task is to create the monthly summations on the line that has just been created. For this I used Formular1c1 but I could not get a logic to make this sum.

Here’s the excerpt from my code:

Sub criar_linha()

Dim txt As String
Dim endcell, total As Variant
Dim columnsRange As Range


txt = "Cliente"
endcell = ThisWorkbook.Worksheets("Teste").Cells.Find(txt, , ,xlWhole).Address
total = ThisWorkbook.Worksheets("Teste").Cells.Find("Total", , ,xlWhole).Column

Set columnsRange = Range(endcell, Range(endcell).End(xlDown)).Offset(1)
Dim cell As Range
Dim range_soma As Range
Dim cell_2 As Range



For Each cell In columnsRange
  If cell.Value = "" Then
        cell.Value = "Subtotal"

  ElseIf cell.Value = "Subtotal" Then

  ElseIf cell.Offset(1).Value <> cell.Value Then
        MsgBox "insere abaixo " & cell.Address
        cell.Offset(1).EntireRow.Insert
        cell.Offset(1).Font.Bold = True
        cell.Offset(1).Value = "Subtotal"
        Range(cell.Offset(1, -2), cell.Offset(1, total - 4)).Interior.ColorIndex = 24

           For Each cell_2 In Range(cell.Offset(1, 1), cell.Offset(1, total - 4))
           cell_2.FormulaR1C1 = "=SUM(R[COUNT IF columnsRange = columnsRange*-1]C:R[-1]C)"
           Next cell_2
    End If
Next cell

End Sub

Answer 1

A way to insert the formula with .FormulaR1C1 is to insert a summation formula that goes from the first cell before Subtotal to a cell before Subtotal.

This can be accomplished with the following formula: .FormulaR1C1 = "=SUM(R[" & (cell_2.Row - FirstRow) * -1 & "]C:R[-1]C)"

That in your example, in the first loop of cell_2, the value of cell_2.Row is 8 and FirstRow is 2. So the formula would be .FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)", that is, performs a sum from 6 lines prior to where the formula will be inserted and ends in the cell that is a previous row, all in the same column.

Code

Follow the full code:

Option Explicit
Sub criar_linha()

    Dim txt As String, endcell As String
    Dim total As Variant
    Dim columnsRange As Range

    txt = "Cliente"
    endcell = ThisWorkbook.Worksheets("Teste").Cells.Find(txt, , , xlWhole).Address
    total = ThisWorkbook.Worksheets("Teste").Cells.Find("Total", , , xlWhole).Column

    Set columnsRange = Range(endcell, Range(endcell).End(xlDown)).Offset(1)
    
    Dim cell As Range, range_soma As Range, cell_2 As Range
    
    Dim IsFirstRow As Boolean
    Dim FirstRow As Long
    
    FirstRow = Range(endcell).Offset(1, 0).Row

    For Each cell In columnsRange
        'Verifica se é a primeira linha
        If IsFirstRow = True Then
            IsFirstRow = False
            FirstRow = cell.Row + 1
        End If
        
        If cell.Value = "" Then
            cell.Value = "Subtotal"

        ElseIf cell.Value = "Subtotal" Then

        ElseIf cell.Offset(1).Value <> cell.Value Then
            IsFirstRow = True
            'MsgBox "insere abaixo " & cell.Address
            cell.Offset(1).EntireRow.Insert
            cell.Offset(1).Font.Bold = True
            cell.Offset(1).Value = "Subtotal"
            Range(cell.Offset(1, -2), cell.Offset(1, total - 4)).Interior.ColorIndex = 24

            For Each cell_2 In Range(cell.Offset(1, 1), cell.Offset(1, total - 4))
                cell_2.FormulaR1C1 = "=SUM(R[" & _
                                     (cell_2.Row - FirstRow) * -1 & _
                                     "]C:R[-1]C)"
            Next cell_2
        End If
    Next cell

End Sub