Excel VBA - transpose formulas in vertical array to horizontal array

Question

I have googled all sorts of phrases for an answer to my question but I'm having a hard time locating a solution that works. It likely involves combination of a few different solutions, or a method I have yet to think of; so any help would be appreciated.

Say I have formulas in cells A1, A2, A3, and A4. Let's say I want those EXACT formulas moved to the right one column.

In VBA I can say:

Range("B1:B4").Formula = Range("A1:A4").Formula

What I'm looking to do is something like this:

Range("B1:E1").Formula = Range("A1:A4").Formula

See how my B:E range is horizontal verses the vertical range of A1:A4.

I have tried all sorts of transpose options but I can't find any that work because I want the EXACT formula's to transfer.

Any thoughts?

Answer 1

You could try something like:

Sub PivotRangeFormulas()
    Dim rngSrc As Range: Set rngSrc = ActiveSheet.Range("A1:A4")
    Dim rngTgt As Range: Set rngTgt = ActiveSheet.Range("B1:E1")
    Dim i As Long: For i = 1 To rngSrc.Rows.Count
        Application.Index(rngTgt, i).Formula = Application.Index(rngSrc, i).Formula
    Next i
End Sub

You could also use an Offset function from the first cell in each range

Answer 2

Range("B1:E1").Formula = WorksheetFunction.Transpose(Range("A1:A4").Formula)