Copying a formula as text?

[terrypin]

I have a worksheet containing information about walks/hikes. I've constructed B27 using C6 and B3. To copy B27 to an external text file I can cheerfully use the familiar Ctrl+C and Ctrl+V commands.

But that doesn't work to copy B27 to another cell. How do I do that please, ideally with VBA, so that the full text shows in the new cell, as it does in B27?

[joeu2004]

Ostensibly:

Range("X27") = Chr(39) & Range("B27").Formula

However, if the formula might be an array formula, the following is more complete:

Range("X27") = Chr(39) & IIf(Range("B27").HasArray, "{ ", "") & Range("B27").Formula & IIf(Range("B27").HasArray, " }", "")

[terrypin]

Thanks, I'm sure I'm getting closer. But still unable to adapt your solution to my scenario.

I ran a simplified macro with my code
My code so far looks like this:

Please Login or Register  to view this content.

It gives this result, namely with Miles and Avg, mph OK but the more complex text cell is wrong. How can I adapt your solution to fix it please?