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
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.
I know. That works fine too. Thought I’d show how you could do it with VBA and use the variant method to pull/push properties from/to a range.
Jan Karel,
It works with Excel 2007 B2TR as well
Kind regards,
Dennis
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
[...] I didn’t initially intend to post tips, but the other day Jan Karel Pieterse from Daily Dose of Excel wrote an excellent post on how to transpose a table while keeping the formulas in the table intact. I was actually asked about this by one of our customers, so I thought I should share this with all of you. [...]
Alternative ?
With Selection.CurrentRegion
.Offset(.Rows.Count + 4).Resize(.Columns.Count, .Rows.Count) = Application.WorksheetFunction.Transpose(.Formula)
End With
End Sub
[...] Pilih range dari tabel matrik yang akan di transpose, dan jalankan vba code tersebut. Sumber: http://www.dailydoseofexcel.com/archives/2006/09/15/transposing-the-formulas-in-a-table-of-cells/ [...]
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
@ Mukesh
You could try:
And
:=False, Transpose:=False
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).