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?

So you are looking for a way to copy and paste transpose formulas, but keep the relative (not '=$A$1') references? — seadoggie01
– seadoggie01, Commented Mar 24, 2016 at 20:12
@seadoggie - actually exactly the opposite. I need the copy/paste to keep the formulas as they are, but my plan is to then copy that row of formulas down to the bottom of my data using LastRow workarounds. — J Taylor
– J Taylor, Commented Mar 24, 2016 at 20:31
@J Taylor - If you don't care about relative references then you can copy and paste special. Select paste formulas and check the transpose option. — seadoggie01
– seadoggie01, Commented Mar 24, 2016 at 20:34

Tragamor · Accepted Answer · 2016-03-24 20:15:24Z

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

answered Mar 24, 2016 at 20:15

Tragamor

3,6893 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J Taylor Over a year ago

I'm not a complete novice with VBA, but I'm also not a coder/developer (I do reporting and data analytics), so reading this and understanding it takes longer for me than it would for others. That said, I think I see what you're doing here. I'm not familiar with the application.index reference but I get the gist of it. I'll give this a shot today/tomorrow and report back findings

J Taylor Over a year ago

So actually, this worked. It is strange though that when I begin typing Application.Index I do not receive the help dialog box, leading me to believe Application.Index is not a valid function. That said this worked perfectly. Thanks so very much!

user3598756 · Accepted Answer · 2016-03-25 08:47:00Z

1

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

answered Mar 25, 2016 at 8:47

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Comments

Bo_ · Accepted Answer · 2016-03-24 21:12:57Z

0

Is locking the cell reference inside your formulas possible? I'm sure you are aware, but the F4 key (pc) will toggle referenced cell locks. The dollar sign locks the column letter or row number [A6, $A$6, A$6, $A6]. If you lock your cell references, you can then copy and transpose the formulas.

Range("B1:E1").Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

Here is another option: Try recording a macro while entering your formula in the cell. If your formula is:

=SUM(D3:D4)

Depending on where you entered the formula, the VBA output might look like:

"=SUM(R[3]C:R[4]C)"

Here is the absolute reference in VBA:

"=SUM(R3C4:R4C4)"

You could then do something like:

Range("A8:A23").FormulaR1C1 = "=SUM(R3C4:R4C4)"

This will enter the formula =SUM($D$3:$D$4) in all the cells from "A8:A23". If you play with the brackets in the VBA formula, you should be able to make it work. The formula below searches the column to the left of the selected cell(s) containing the formula for the text "nff":

    Selection.FormulaR1C1 = _
    "=SEARCH(""nff"",RC[-1])"

edited Mar 24, 2016 at 21:12

answered Mar 24, 2016 at 20:17

Bo_

216 bronze badges

1 Comment

J Taylor Over a year ago

@Bo - yes it's possible, however the next step of my code will be to drag those formulas down to the bottom of my data so I need the references to be relative when they go in. I suppose I could make them absolute, use the copy/paste transpose function, and then make them relative again in the new location through another line of VBA code...

Collectives™ on Stack Overflow

Excel VBA - transpose formulas in vertical array to horizontal array

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related