Macro to flip horizontal cells to vertical and keep formulas

[Jarke]

Hi,

I'm looking for a macro to flip horizontal cells to vertical that also keeps the formulas. Does anyone have one in store?

Could be basic, and start in active sheet in A1 etc.

Thanks in advance!

 

[JLGWhiz]

You could try:

Range("A1:E1").Copy
Range("A2").PasteSpecial xlPasteAll, Transpose:=True

You would need to substitute your actual range and destnation anchor cell.

 

[Jarke]

Hi mate, thanks. But it does not lock the formulas. My goal is that the formulas in horizontal will be the same when it's turned into vertical alignment. Any idéas for that?

 

[arunsjain]

Try this, if it works for you

Sub Transposewithformula()
    Cells.Replace What:="=", Replacement:="#=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Range("A1:E1").Copy
            
    Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Application.CutCopyMode = False
    
    Cells.Replace What:="#=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Jarke]

Thank you for your reply! Yes something like that. I noticed a problem tho, I need to keep the formulas in arrays "{ }", would you know how to add that to the VBA formula?

 

[arunsjain]

Try this one. Second part will keep formula in { }.

Sub Transposewithformula()
    
    Dim fArray As Variant
    Dim Rng As Range
    
    
    Cells.Replace What:="=", Replacement:="#=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Range("A1:E1").Copy
            
    Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Application.CutCopyMode = False
    
    Cells.Replace What:="#=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

With Sheet1
    fArray = .Range("A2:A6").FormulaR1C1
    Set Rng = .Range("A2:A6")
    Rng.Formula = fArray
    Rng.FormulaArray = Rng.FormulaR1C1
End With
End Sub