Getting only value out of a formula
 

Hi

I have a formula in a cell, but i want only the result of the formula
for my macro. Is there a way for me to reference the value in the cell
e.g
Let say A1 contains "=1+1"
how can i get the value 2 from cell A1 from VBA

Getting only value out of a formula
 

Hi

Just an example

Code:
--------------------

Sub test()
Dim r As Range
For Each r In Range("a1:a100")
If r.HasFormula Then
r.Value = r.Value
End If
Next
End Sub
--------------------


or

Code:
--------------------

Sub test2()
With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
.Value = .Value
End With
End Sub
--------------------


crusoe81 Wrote:
Hi

I have a formula in a cell, but i want only the result of the formula
for my macro. Is there a way for me to reference the value in the cell
e.g
Let say A1 contains "=1+1"
how can i get the value 2 from cell A1 from VBA


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=375903

Getting only value out of a formula
 

Simply use something like this in your macro:

Sub MySub()
myVar = Worksheets("Sheet1").Range("A1")
End Sub

The above line will assign the value in cell A1 in sheet Sheet1 to a
variable called myVar.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375903

Getting only value out of a formula
 

?Range("A1").Value
Since Value is default for a formula, you can also use
?Range("A1")
Which can be simplified to
?[A1]

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"crusoe81" wrote in message
...
Hi

I have a formula in a cell, but i want only the result of the formula
for my macro. Is there a way for me to reference the value in the cell
e.g
Let say A1 contains "=1+1"
how can i get the value 2 from cell A1 from VBA

Getting only value out of a formula
 

Just some added info:
Simplifying to [A1]
is not recommended (in my opinion) as it is many times slower

--
Regards,
Tom Ogivly

"Niek Otten" wrote in message
...
?Range("A1").Value
Since Value is default for a formula, you can also use
?Range("A1")
Which can be simplified to
?[A1]

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"crusoe81" wrote in message
...
Hi

I have a formula in a cell, but i want only the result of the formula
for my macro. Is there a way for me to reference the value in the cell
e.g
Let say A1 contains "=1+1"
how can i get the value 2 from cell A1 from VBA

Getting only value out of a formula
 

Thanks, Tom, I wasn't aware of that. In fact, I wasn't aware of the feature
at all until some time ago. It's a pity it has disadvantages, because I
quite liked the reduced typing!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Tom Ogilvy" wrote in message
...
Just some added info:
Simplifying to [A1]
is not recommended (in my opinion) as it is many times slower

--
Regards,
Tom Ogivly

"Niek Otten" wrote in message
...
?Range("A1").Value
Since Value is default for a formula, you can also use
?Range("A1")
Which can be simplified to
?[A1]

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"crusoe81" wrote in message
...
Hi

I have a formula in a cell, but i want only the result of the formula
for my macro. Is there a way for me to reference the value in the cell
e.g
Let say A1 contains "=1+1"
how can i get the value 2 from cell A1 from VBA