vba/excel - How to use RC notation to copy formula from different sheet while maintaining relative references

Question

I am trying to copy a formula from another sheet ("SCE_Tables") to a central sheet ("Main"). "SCE_Tables" holds reference tables that I am copying over to main, based on selections from a form that the user fills out.

The formula in "SCE_Tables", uses RC-notation to subtract values from columns 2 to the left ('Prebill'), and 1 to the left ('Post BESS Bill') of the cells in the 'BESS Savings' column: In the highlighted cell below:

Formula: =RC[-2]-RC[-1] . This cell is at Row 15, Column 6

The issue arises when I try to fill the "Main" sheet range with these tables. The "Main" sheet range where these tables go have the same format as the reference tables: The highlighted cell below (on the "Main" sheet), is at Row 21, Column 18. The formula in this cell is:

Formula: =R[-6]C[-14]-R[-6]C[-13]

The RC reference that is copied over to the "Main" sheet converts the RC reference back to that in the "SCE_Tables" sheet, resulting in the formula trying to calculate:

(R15, C4) - (R15, C5) on the main sheet, when my desired formula is:

(R21, C16) - (R21, C15)

Is there a way I dynamically create a formula that calculates the difference between the cells 2 to the left and 1 to the left of the cell in the "Main" sheet?

Edit: Including some of my VBA code To process the ListBox selections:

Public Sub FormatListboxSelections(ByVal selectionNameArray As Variant)
    testInt = 0

    'Entire possible area for tables
    Dim tableRange As Range
    Set tableRange = Range("TableRange")
    Dim SDR As Range
    Set SDR = Range("ScenarioDetailsRange")
    
    
    'Electricity Provider
    Dim i As Integer, elecProvider As String
    elecProvider = Range("input_electricity_provider").Value
    
    'Clear range
    tableRange.ClearContents
    SDR.ClearContents
    
    Dim currRow As Integer, leftCol As Integer, startingCell As Range
    currRow = 1
    
    'Loop through all selections
    For i = LBound(selectionNameArray) To UBound(selectionNameArray)
        Dim selectionTableObj As Variant
        
        Dim scenario As String
        scenario = selectionNameArray(i)
        'If arr(i) == 0, we've reached end of the selected values
        If Len(scenario) = 0 Then
            Debug.Print "Exiting loop at index " & i
            Exit For
        Else
            'Check for each scenario type
            'PV only scenario
            If scenario = "PV Only" Then
                Set selectionTableObj = HandlePVOnly(electricityProvider:=elecProvider, scenario:=scenario)
            ElseIf scenario = "2hr Only" Or scenario = "4hr Only" Then
                Set selectionTableObj = HandleBESSOnly(electricityProvider:=elecProvider, scenario:=scenario)
            Else
                Set selectionTableObj = HandleComboScenario(electricityProvider:=elecProvider, scenario:=scenario)
            End If
        selectionTableObj.UpdateAllFields
        testInt = testInt + 1
        End If
        
        Dim x As Integer
        'Iterate through all rows in the selected table
        For x = 1 To selectionTableObj.m_table.Rows.Count
            Dim dummy As Variant
            Set dummy = Application.WorksheetFunction.Index(selectionTableObj.m_table.Rows, x, 0)
            Dim selectionTableRow As Variant
            Set selectionTableRow = selectionTableObj.m_table.Rows(x)
            
            'Update single row
            '***HERE IS WHERE THE SELECTED TABLES GET WRITTEN TO THE MAIN 
            'WORKSHEET***
            Dim j As Integer
            For j = 1 To dummy.Columns.Count
                tableRange(currRow, j).Formula = dummy(1, j).Formula
            Next j
        
            'increment currRow
            currRow = currRow + 1
        
        Next x
        
    Next
    
    'If full selection, also show respective extra inputs
    If UBound(selectionNameArray) = 7 Then
        PopulateExtraInputs elecProvider
    End If
End Sub

To handle BESS scenarios (one of 3 scenario types the user can select with the form), I use a Factory module and call the 'InitiateProperties' subroutine in the BESSScenarioTable class module:

Public Function CreateBESSScenarioTable(ByVal table As Range, scenario As String, scenarioDetailsArray As Variant, tableLength As Integer, ByVal result As ScenarioResult, tariffs As Variant)
    Set CreateBESSScenarioTable = New BESSScenarioTable
    CreateBESSScenarioTable.InitiateProperties table:=table, scenario:=scenario, scenarioDetailsArray:=scenarioDetailsArray, tableLength:=tableLength, result:=result, tariffs:=tariffs
End Function

'Constructor
Public Sub InitiateProperties(table As Range, scenario As String, scenarioDetailsArray As Variant, tableLength As Integer, result As ScenarioResult, tariffs As Variant)
    Set m_table = table
    'Using Formula instead of Value to capture column relationships in "Source of Truth" tables (PGE_Tables, SDGE_Tables, SCE_Tables worksheets)
    Debug.Print "Address: " & m_table.address
    Dim xc As Integer, yc As Integer
    For xc = 1 To m_table.Rows.Count
        For yc = 1 To m_table.Columns.Count
            Debug.Print "(" & xc & ", " & yc & ") - Formula: " & m_table.Cells(xc, yc).Formula
        Next yc
    Next xc
    m_table.FormulaR1C1 = table.FormulaR1C1
    m_scenario = scenario
    m_scenarioDetailsArray = scenarioDetailsArray
    m_tableLength = tableLength
    Set m_result = result
    m_tariffs = tariffs
End Sub

Answer 1

Figured it out - turns out in my VBA code where I was copying the table over, I needed to use tableRange(currRow, j).FormulaR1C1 = dummy(1, j).FormulaR1C1, instead of what I had before: tableRange(currRow, j).Formula = dummy(1, j).Formula

This link was helpful in learning about the different notations Excel uses: https://powerspreadsheets.com/r1c1-formular1c1-vba/