VBA: Add multiple same size ranges in a cumulative way

So I have multiple columns of the same size and equally spaced, which I want to add altogether in the first two columns of the sheet.

Sub flujos_SPC()

    Dim spcode As Range
    Dim codigos As Worksheet: Set codigos = ThisWorkbook.Worksheets("mp pruebas")
    Set spcode = codigos.Range("F6", codigos.Range("F6").End(xlDown))
    
    Dim remat As Worksheet: Set remat = ThisWorkbook.Worksheets("r.matem (2)")
    Dim destino As Worksheet: Set destino = ThisWorkbook.Worksheets("Flujos por SPC")
    Dim cell As Range
    
    Dim col As Integer: col = 5
    Dim contador As Integer: spcode.Count
    
    For Each cell In spcode
        remat.Range("I3") = cell.Value
        With destino
            .Cells(1, col) = "SP Code"
            .Cells(2, col) = cell.Value
            .Range(.Cells(1, col + 1), .Cells(1225, col + 2)) = remat.Range("Q11", "R1235").Value
     
        End With
        col = col + 4
    Next cell
   
End Sub

The columns are generated as such and they all have numeric values, positive non integers for the most part. I thought about adding the ranges the same way you add to the same variable (x = x + 1), but I get mismatch type errors. Ideally, what I would like to do would be to embbed a cumulative sum inside the for each loop, such as:

Sub flujos_SPC()

    Dim spcode As Range
    Dim codigos As Worksheet: Set codigos = ThisWorkbook.Worksheets("mp pruebas")
    Set spcode = codigos.Range("F6", codigos.Range("F6").End(xlDown))
    
    Dim remat As Worksheet: Set remat = ThisWorkbook.Worksheets("r.matem (2)")
    Dim destino As Worksheet: Set destino = ThisWorkbook.Worksheets("Flujos por SPC")
    Dim cell As Range
    
    Dim col As Integer: col = 5
    Dim contador As Integer: spcode.Count
    
    destino.range("A2","B1225") = 0
    For Each cell In spcode
        remat.Range("I3") = cell.Value
        With destino
            .Cells(1, col) = "SP Code"
            .Cells(2, col) = cell.Value
            .Range(.Cells(1, col + 1), .Cells(1225, col + 2)) = remat.Range("Q11", "R1235").Value
     
            .range("A2","B1225") = .range("A2","B1225") + .Range(.Cells(1, col + 1), .Cells(1225, col + 2))
        End With
        col = col + 4
    Next cell
   
End Sub

But then again, I get errors when trying this, even when adding ".value" to each range. I would like some help with this and preferably avoid the use of for loops, mostly for performance reasons. Thanks in advance.

Solution 1:

You could build a formulaR1C1 string.

    Dim f as String
    For Each cell In spcode
        f = f + "+RC[" & col & "]"
        remat.Range("I3") = cell.Value
        With destino
            .Cells(1, col) = "SP Code"
            .Cells(2, col) = cell.Value
            .Range(.Cells(1, col + 1), .Cells(1225, col + 2)) = remat.Range("Q11", "R1235").Value
        End With
        col = col + 4
    Next cell
    destino.Range("A2", "B1225").FormulaR1C1 = "=" & f