VBA Transpose From Specific Number of Cells Above

Question

I've been working on a VBA code, this is only a minor part of my project for reviewing accounting transactions.

The code not included inserts blank rows where there have been transactions using multiple account names. I want to transpose the multiple account names on each transactions where necessary. The "RowNum" code references the number count of account names on that particular transaction. I was hoping to insert that number into my FormulaR1C1.

Sub cellnum()
 
RowNum = "=R[-1]C[4]"
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=transpose(R[-" & RowNum.Value & "C[-1]:R[-1]C[-1])"
End Sub

Answer 1

If you want to put the formula in a different cell than the active cell you need to set the range for that cell first.

for example something like should give the right result

Set rng = Range("A1") 
rng.FormulaR1C1 = "=transpose(R[-" & RowNum.Value & "C[-1]:R[-1]C[-1])"

Im new to VBA and still learning but I think this is the way to go.