working with formula in cell instead of a value

[Quinn]

I'm using VBE to take a row from Excel to transpose. Some of the cells,though, contain formulas (e.g. ="Patient Disposition") instead of values ( e.g. Patient Dispositon). So, I get the ERROR 13 , when trying to do something like:
Array2 = Array(Range("A1:V1").Offset(X, 0).Value)
'transpose rows to become 2nd column
Range("B15:B36").Value = Application.WorksheetFunction.Transpose(Array2)
any ideas?
Thanks:beerchug: "Life's too short for American Beer"

[mdmackillop]

Hi Quinn,
I can't think how to do this, but I created this KB to transpose and create a link to the original data which might give you a workaround.
Regards
MD
http://vbaexpress.com/kb/getarticle.php?kb_id=144

[Norie]

Quinn

Why use that method?

Could you not use PasteSpecial with the Transpose argument as true?

Using WorksheetFunction.Transpose can be useful but it can also throw up various errors.

Also try removing the Array part from your code.

Array2 = Range("A1:V1").Offset(X, 0).Value