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).

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