VBA How can I add a formula that multiplies two values in a Dynamic Table?

Question

I don't have much experience asking these questions so please bare with me. I have the following scenario. I have a big range of data that I'm trying to automate and need to perform some calculations. On column E, I've written code that runs through the entire report and adds a formula on each empty cell found, which divides the total amount of sales (subtotal in column F) by the interest amount (Total in Column G).

Now that I have this new value in column E, I need to run an independent multiplication for each entry in Column G. I need to multiply the subtotal amount I have in Column E by each individual amount in Column F in order to get the corresponding interest for owed interest for each row.

The main problem I'm encountering is that I can't figure a way to fill in the entire Column G with the correct formula since the length of each subset is constantly changing (dynamic).

This is how the table looks like, I have over 20 books but just simplifying it:

Any ideas or help will be much appreciated.

This is the code I've written so far:

Dim Rng As Range, r As Range

    Set Rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    'Top = Selection.End(xlUp).Value

For Each r In Rng.Areas
    With r
         .Cells(1, 1).Offset(.Rows.Count).Formula = "=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, 2) / OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, 1)"
    End With
Next

Answer 1

Try something like this -

Sub Tester()
    
    Dim Rng As Range, rE As Range, rG As Range

    Set Rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    
    For Each rE In Rng.Areas
        Set rG = rE.EntireRow.Columns("G") 'corresponding G range
        
        rG.Formula = "=" & rE.Cells(1).Offset(0, 1).Address(False, False) & _
                     "*" & rE.Cells(rE.Cells.Count).Offset(1).Address(True, True)
        
    Next
End Sub