How to save the results of .Formula in a variable (VBA)

[Colvic]

I want to save the result of this code in a variable instead of cell A1

Sheets("Sheet1").Range("A2").Formula = "=RoundUp(A1, 0)"

Does anyone have a suggestion?


Thanks in advance

 

[strongm]

Something like:

Dim result As Long
result = WorksheetFunction.RoundUp(Range("A1"), 0)

Note that VBA has its own rounding functions that you could use instead, but most produce subtly different results from the worksheet rounding functions.

 

[UnsolvedCoding]

Or you could do it as a variable or constant

To make it a variable do something like this -
Dim MyFormula as string
MyFormula = "=RoundUp(A1, 0)"

To use it as a constant do something like this -
Const MyFormula = "=RoundUp(A1, 0)"

Either would let you use MyFormula anywhere in the code.

 

[UnsolvedCoding]

It looks like I was mis-understanding what you want to do.

If this is below you my apologies, after thinking about it I am not sure by your question exactly what you mean so rather than make assumptions I will start with basics.

If you have something that is in a cell you simply create a varaible and name it and do a Dim statment. Dim has to do with reserving memory just like Const reserves things in memory. The difference is that DIM means it can change while running the code where Const can only change when you alter the actual statement.

For what you ask about, you need to select the type of varaible to use - Integer, Long, Double etc. For this example I am using integer.

DIM MyFormula as integer

MyFormula = Range("A1").Value

The value of whatever number was in cell A1 will now be saved and can be used later.

To put the value of MyFormula in a cell reverse the process.

Range("A1").value = MyFormula

Whatever is on the left side of the = will get the value of what ever is on the right side.

 

[visible2you]

Sheets("Sheet1").Range("A2").Formula = "=RoundUp(A1, 0)"
result = Sheets("Sheet1").Range("A2").value
Sheets("Sheet1").Range("A2").ClearContents