• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4740
  • Last Modified:

[ EXCEL ] Storing a cell formula in VBA

In VBA I want to grab a formula in a cell of my worksheet, save it in a string so that later I can affect it to an other cell,I'm unable to grab the formula and if i put it staticly in VBA it always put a space in front of my equal sign so it see's it like text.


I don't want to do a:
    Range("C2:O2").Select
    Selection.Copy

Because I want to save it for later.  Her is my code

    Dim CellC2 As String
    Dim Formula As string
   
    CellC2 = Cells(2, 3) ' this only transfer the result, not the formula
    'This is the formula in my Cells, I save it staticly
    Formula = "=""Au "" & TEXT(IF(WEEKDAY(TODAY())=2;TODAY()-3;TODAY()-1);""jj mmmm aaaa"")"
   
    Cells(3, 3).Value = Formula ' Save it in C3 but it put a space in front of the equal sign, so it's interpreted as text


Thank you very much for your help
0
maleger
Asked:
maleger
1 Solution
 
byundtCommented:
The following code will grab the formula, then transfer it as you requested:

Dim temp
temp=Cells(2,3).Formula
cells(3,3).Formula=temp
0
 
AKbor75Commented:
why don't you use cells(1,1).formula = cells(2,2).formula ?
i don't think you need a buffer if you don't want to make changes to the formula.

you can also copy a range (includes values, formulas etc):
cells(1,1).copy dest:=range( cells(2,2), cells(10,2) )
0
 
70sgirlCommented:
Sub temp()
Dim temp As String

temp = Cells(2, 3).Formula
Cells(3, 3).Value = " " & temp

End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now