Paste Special Transpose does not update cell references as expected

[michaelmmoses]

I am writing a macro that copies and transposes cells containing formulas with absolute column reference, but not absolute row references; e.g. $C3. I use paste special transpose expecting it to update the row numbers, and it does not. Instead it populates several rows with the same formula referencing $C3. I notice that paste special works the same way from the menu. How do I make the row references update?

[joe31623]

I would take the formula's cell reference and convert it to long datatypes: row = Range(cell).row/column = Range(cell).column, then get a new cell: newcell = Range(column,row), with transposed row column numbers, and remove the absolute referencing if required with something like newcellarray = split(newcell,"$") and re-build the newcell string.

Maybe there is a better solution, but I'd write a macro to do that -- should be about 50 lines.

Unless someone has a better idea and you still need help, please post an example (simplified) worksheet with a tab containing desired results and a tab containing the original formulas.