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

Is there a way I can offset from where the formula is being input and not from my active cell? — John Beauchamp, Sep 28 '20 at 18:11

score 0 · Answer 1 · answered Sep 28 '20 at 19:31

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.

VBA Transpose From Specific Number of Cells Above

1 Answers1