Convert a formula in a cell to it's result
 

I have a cell with a formula which returns a date. I would like to convert
the formula in the cell to it's result if another cell is 0. Can I do that?

Convert a formula in a cell to it's result
 

Hi BHTay

You can use the change event to do this

if another cell is 0
Do you change this cell manual or is it also a formula ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BHTay" wrote in message ...
I have a cell with a formula which returns a date. I would like to convert
the formula in the cell to it's result if another cell is 0. Can I do that?

Convert a formula in a cell to it's result
 

Hi Ron de Bruin,
Thanks for the quick response.

The other cell is a fomula.

"Ron de Bruin" wrote:

Hi BHTay

You can use the change event to do this

if another cell is 0
Do you change this cell manual or is it also a formula ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BHTay" wrote in message ...
I have a cell with a formula which returns a date. I would like to convert
the formula in the cell to it's result if another cell is 0. Can I do that?

Convert a formula in a cell to it's result
 

Try this event in a sheet module

If the formula in B1 0 then the formula in A1 change to a value

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("B1"), rng) Is Nothing Then
If Range("B1").Value 0 Then
Range("A1").Value = Range("A1").Value
End If
End If
End If
EndMacro:
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BHTay" wrote in message ...
Hi Ron de Bruin,
Thanks for the quick response.

The other cell is a fomula.

"Ron de Bruin" wrote:

Hi BHTay

You can use the change event to do this

if another cell is 0
Do you change this cell manual or is it also a formula ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BHTay" wrote in message ...
I have a cell with a formula which returns a date. I would like to convert
the formula in the cell to it's result if another cell is 0. Can I do that?

Convert a formula in a cell to it's result
 

Thanks Ron. This seems to do the job.

"Ron de Bruin" wrote:

Try this event in a sheet module

If the formula in B1 0 then the formula in A1 change to a value

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("B1"), rng) Is Nothing Then
If Range("B1").Value 0 Then
Range("A1").Value = Range("A1").Value
End If
End If
End If
EndMacro:
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BHTay" wrote in message ...
Hi Ron de Bruin,
Thanks for the quick response.

The other cell is a fomula.

"Ron de Bruin" wrote:

Hi BHTay

You can use the change event to do this

if another cell is 0
Do you change this cell manual or is it also a formula ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BHTay" wrote in message ...
I have a cell with a formula which returns a date. I would like to convert
the formula in the cell to it's result if another cell is 0. Can I do that?