Paste text as formula

I am creating a string that is a formula. Like in here (this is a simpler example)

If:

A1 is "Sum"
A2 is "D3"
开发者_开发百科

Then B1 is =Concatenate("=",A1,"(",A2,")")

I want a VBA macro that takes the result of the formula in B1 and paste is as a formula in C1.

I need C1 to be the formula =SUM(D3)

I think it involves the PasteSpecial and evaluate, but I can't figure out how.

I don't want to use the INDIRECT function because I want to be able to fill more cells using than formula and the relative references inside.

with Activesheet
  .Range("C1").Formula = .Range("B1").Value
End With

I don't know if you explicitly want a command macro, but this thing seems like a great use of a VBA UDF. If you create the UDF:

Function EvalFormula(f As String) As Variant
    EvalFormula = Application.Evaluate(f)
End Function

Then in C1 you can call:

=EvalFormula(B1)

Writing this as a UDF is going to eliminate those unpleasant situations where you forgot to run your macro and now your sheet is all out of whack.

I used ActiveSheet.Cells(5, 3).Formula = ActiveSheet.Cells(5, 3).Value to use the text value as a formula

ActiveSheet.Cells(2, 2).Value = ActiveSheet.Cells(1, 2).Value

You can work the rest from that I'd assume :-)