Convert a formula in a cell to it's result

[BHTay]

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?

[Ron de Bruin]

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" <BHTay@discussions.microsoft.com> wrote in message news:9E670E9B-2151-47C4-8852-045B8ED6A2AD@microsoft.com...
>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?

[BHTay]

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" <BHTay@discussions.microsoft.com> wrote in message news:9E670E9B-2151-47C4-8852-045B8ED6A2AD@microsoft.com...
> >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?
>
>
>

[Ron de Bruin]

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" <BHTay@discussions.microsoft.com> wrote in message news:C01F25B9-3A73-4868-A023-65DA3CEB26FA@microsoft.com...
> 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" <BHTay@discussions.microsoft.com> wrote in message news:9E670E9B-2151-47C4-8852-045B8ED6A2AD@microsoft.com...
>> >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?
>>
>>
>>

[BHTay]

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" <BHTay@discussions.microsoft.com> wrote in message news:C01F25B9-3A73-4868-A023-65DA3CEB26FA@microsoft.com...
> > 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" <BHTay@discussions.microsoft.com> wrote in message news:9E670E9B-2151-47C4-8852-045B8ED6A2AD@microsoft.com...
> >> >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?
> >>
> >>
> >>
>
>
>