Transposing the Formulas in a Table of Cells

OK, time for some non Excel 2007 stuff…

Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that.

What the poster wanted is to go from:

To:

Read on here.

Regards,

Jan Karel Pieterse

JKP Application Development Services

Founding member of:
Professional Office Developers Association

Posted in Uncategorized

11 thoughts on “Transposing the Formulas in a Table of Cells

  1. Great VBA Solution JKP!

    I often get this type of question, but usually from non-technical users.
    I typically give them this solution.

    – Find and Replace the equal (=) signs in the formulas with number (#) signs.
    – Transpose the range
    – Find and Replace the number (#) signs back into equal (=) signs.

    Since Excel does not recognize the formulas, it does not adjust the references.

  2. Jan Karel,

    I always wanted an “Copy Formula” option in the Dragdrop menu (when u right click on the border of a range of cell…drag and let go….)

    Inspired by the Custom Drag Drop file from Stephen and your post… I finally manged to do it !!

    Thanks to both of you…

    Regards
    Sam

  3. Alternative ?

    Sub test()
      With Selection.CurrentRegion
        .Offset(.Rows.Count + 4).Resize(.Columns.Count, .Rows.Count) = Application.WorksheetFunction.Transpose(.Formula)
      End With
    End Sub
  4. i am copying a date type value from one cell to another cell using following syntax but it is not properly copying

    1.Selection.PasteSpecial Paste:=xlPasteValues

    2.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

  5. @ Mukesh
    You could try:

    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    And

    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
  6. Mukesh –

    Does the value look like ‘39903’? You have to copy not just the value but also the number format, to turn that into 31-March-2009 (or whatever format you like).

  7. Easiest way is described by “Mike Alexander”

    – Find and Replace the equal (=) signs in the formulas with number (#) signs.
    – Transpose the range
    – Find and Replace the number (#) signs back into equal (=) signs.

    Thanx Mike


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.