Use VBA to create and insert an indirect relative formula

I imagine it is, but I can't seem to get it quite to work. I'm two days into youtube videos and forum hunting and could use some help!

I think I'm close with object variables as a range but I can't seem to get the formula to use the formula contained in another cell, much less offset a given amount from that range. I tried it as a string, but then I couldn't use offsets.

I want to:

1st) define a variable as a range (a single cell) referenced in formula(not the value) in another cell
2nd) use that range with offsets to make a FormulaR1C1 formula.

Sub Choose1()

   Dim Fir As Range
         
    Set Fir = ActiveCell
    
           
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(fir.offset(0,1),fir.offset(10,1),fir.offset(20,1)"

End Sub

Say Cell A1{activecell} = A3

And I want the Formula in B1{always activecell.offset(0,1)} to = Sum(B3{always the same row number as the formula in A1, and offset 1 column to the right},B13{always activecell.offset(10,1)},B23{always activecell.offset(20.1)})

Please help!

cdratz,
It seems it is the first time you are with us, but there is some rules:
Code tags when posting code
Explicit Title
Can you update your thread

Hello cdratz,

Welcome to the Forum!

I changed the title for you.

This VBA macro will place the relative R1C1 formula in the cell directly to the right of the active cell. The formula will sum the cells in the same column as the formula 2 rows down, 12 rows, and 22 rows down. Using cell A1 as the active cell the summed cells are B3, B13, and B23.

Sub Test()

    Dim col As Variant
    Dim row As Long
    
        row = ActiveCell.row
        col = ActiveCell.Offset(0, 1).Column
        
        Cells(row, col).FormulaR1C1 = "=SUM(R[2]C,R[12]C,R[22]C)"
    
End Sub

EDIT:
After looking at this again, I think this the macro you want to use. The first macro does not use the row number of the cell in A1. This one does.

Sub Test2()

    Dim col As Variant
    Dim r   As Long
    Dim row As Long
    
        r = Range(ActiveCell).row
        row = ActiveCell.row
        col = ActiveCell.Offset(0, 1).Column
        
        Cells(row, col).FormulaR1C1 = "=SUM(R" & r & "C,R" & r + 10 & "C,R" & r + 20 & "C)"
    
End Sub

Hello cdratz,

The second macros works for me. Since you are using Excel 2010 and I am too, there should be no compatibility issues.

Do you have code elsewhere in the workbook?

EDIT:
I was able to reproduce the error by changing the A1 to "=A3". This is not a valid range reference in Excel or VBA. This will return the value of A3. Put "A3" in cell A1 and it will work.