[ EXCEL ] Storing a cell formula in VBA

Posted on 2003-03-17
Medium Priority
Last Modified: 2006-11-17
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:

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
Question by:maleger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 81

Accepted Solution

byundt earned 150 total points
ID: 8155600
The following code will grab the formula, then transfer it as you requested:

Dim temp

Expert Comment

ID: 8158357
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) )

Expert Comment

ID: 8160793
Sub temp()
Dim temp As String

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

End Sub

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question