Need help converting formula into value in Excel

[prakashseth]

Hi friends, I know the easiest way is to copy and use paste special then "value" options. but is there a way to make it automated in case the desired output value has resulted.

example:
A1 B1 C1 D1
1 2 A1+B1 Yes/No

on the above example, if D1=Yes, the output C1 will become value equals to 3 and the formula will be remove, if D1=No, the formula will still remain.

Thanks in advance for any help

[kelfro]

This can be done with a worksheet change event so whenever D1 changes the code will kick in

code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
If Target = "Yes" Then
Target.Offset(0, -1) = Range("A1") + Range("B1")
Else
Target.Offset(0, -1) = "=A1+B1"
End If
End If
End Sub

This can only be done manually, like you said by copy and pasting special values or with VBA.


code:

Sub Button2_Click()
If Range("D1") = "Yes" Then
Range("C1") = Range("A1") + Range("B1")
Else
Range("C1") = "=A1+B1"
End If
End Sub

[prakashseth]

Thanks kelfro. i tried both tricks it works.