Get back a formula from a cell - VBA

Question

I'm writing a VBA code, in which I need to reference in one sheet a formula from a different sheet for instance: in sheet A in cell (1,3) there's a formula

"=R[-1]C*R[-2]C"

and I want in sheet B in cell (1, 3) to put the formula

"=A!R[-1]C*A!R[-2]C"

I want to save in a string only the formula -

"=R[-1]C*R[-2]C"

And manually do the adjustments I need.

So my question is:

• How can I get the formula in the cell? when I write "temp=worksheets("A").cells(1,3)",
  I get back the value from the cell, and not the formula...

Answer 1

You can get (and set) the formula in a cell (A1 in this case) using, strangely enough:

Range("A1").Formula

(see here for details).

That just comes back as a string so you can evaluate/manipulate it with the regular VBA string functions like left, right, mid, instr and so on.

As per the linked page, the formula returned for a constant cell is just the constant itself, and a blank cell returns "".

You can use that information to decide if it's actually formulaic or not.