Help With Runaway Calculation
 

Hi All,

I need a little guidance. I'm trying to create a Sub that converts
temperature from Fahraneit to Celcius and Celcius to Fahrenheit with the
Worksheet_Change(ByVal Target As Excel.Range).
In my sheet I have set up K20 for inputing Deg F and M20 for inputing Deg C.
When you change the value in K20 the corresponding Celcius value is
calculated in M20 and vise versa.

I have often used the following routine for conversions such as this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set isect = Intersect(Target, Range("K20"))
If Not isect Is Nothing Then
Range("M20").Value = ((Target.Value - 32) * (5 / 9))
End If
Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = ((Target.Value + 32) * (9 / 5))
End If
End Sub

Usually I just multiply or divide the "Target.Value" by a single integer but
with the scenario above the values in the cells runaway out of control and
return some strange value.
Can someone help shed some light on this.
I'm open to any kind of alternate method of making the conversion happen
when I enter a value into one cell or the other.

If more clarity is required please let me know.

TIA

Pete

Help With Runaway Calculation
 

Pete

I've changed both sides this time.

Tony
Set isect = Intersect(Target, Range("K20"))
If Not isect Is Nothing Then
Range("M20").Value = ((Target.Value - 32) * 5 / 9)
End If
Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = (Target.Value * 9 / 5 + 32)
End If

----- Pete Csiszar wrote: -----

Hi All,

I need a little guidance. I'm trying to create a Sub that converts
temperature from Fahraneit to Celcius and Celcius to Fahrenheit with the
Worksheet_Change(ByVal Target As Excel.Range).
In my sheet I have set up K20 for inputing Deg F and M20 for inputing Deg C.
When you change the value in K20 the corresponding Celcius value is
calculated in M20 and vise versa.

I have often used the following routine for conversions such as this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set isect = Intersect(Target, Range("K20"))
If Not isect Is Nothing Then
Range("M20").Value = ((Target.Value - 32) * (5 / 9))
End If
Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = ((Target.Value + 32) * (9 / 5))
End If
End Sub

Usually I just multiply or divide the "Target.Value" by a single integer but
with the scenario above the values in the cells runaway out of control and
return some strange value.
Can someone help shed some light on this.
I'm open to any kind of alternate method of making the conversion happen
when I enter a value into one cell or the other.

If more clarity is required please let me know.

TIA

Pete

Help With Runaway Calculation
 

Pete

I think you have an incorrect calcuation. Converting from C to F is C*9/5+32 so the routine should read

Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = (Target.Value * (9 / 5) + 32)

Tony

----- Pete Csiszar wrote: -----

Hi All,

I need a little guidance. I'm trying to create a Sub that converts
temperature from Fahraneit to Celcius and Celcius to Fahrenheit with the
Worksheet_Change(ByVal Target As Excel.Range).
In my sheet I have set up K20 for inputing Deg F and M20 for inputing Deg C.
When you change the value in K20 the corresponding Celcius value is
calculated in M20 and vise versa.

I have often used the following routine for conversions such as this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set isect = Intersect(Target, Range("K20"))
If Not isect Is Nothing Then
Range("M20").Value = ((Target.Value - 32) * (5 / 9))
End If
Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = ((Target.Value + 32) * (9 / 5))
End If
End Sub

Usually I just multiply or divide the "Target.Value" by a single integer but
with the scenario above the values in the cells runaway out of control and
return some strange value.
Can someone help shed some light on this.
I'm open to any kind of alternate method of making the conversion happen
when I enter a value into one cell or the other.

If more clarity is required please let me know.

TIA

Pete

Help With Runaway Calculation
 

Pete


Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Stop
If Not (IsNumeric(Target.Value)) Or Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
Select Case Target.Address
Case "$K$20"
Range("M20").Value = ((Target.Value - 32) * (5 / 9))
Case "$M$20"
Range("K20").Value = ((Target.Value + 32) * (9 / 5))
End Select
Application.EnableEvents = True
End Sub


Application.EnableEvents = False is required to stop the change events
being triggered wheen the script changes the value of a cell

Application.EnableEvents = True is required so that the change event is
triggered next time user changes a cell enty


---
Message posted from http://www.ExcelForum.com/

Help With Runaway Calculation
 

Pete

I posted the wrong version of my code

This is what I meant to post

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Select Case Target.Address
Case "$K$20"
If Not (IsNumeric(Target.Value)) Or Target.Value = "" Then
Range("M20").ClearContents
Else
Range("M20").Value = ((Target.Value - 32) * (5 / 9))
End If
Case "$M$20"
If Not (IsNumeric(Target.Value)) Or Target.Value = "" Then
Range("K20").ClearContents
Else
Range("K20").Value = ((Target.Value + 32) * (9 / 5))
End If
End Select
Application.EnableEvents = True
End Sub


---
Message posted from http://www.ExcelForum.com/

Help With Runaway Calculation
 

Thanks Tony,
It works like a charm.
It kills me how something so simple could hang me up.

Regards,
Pete

"acw" wrote in message
...
Pete

I think you have an incorrect calcuation. Converting from C to F is
C*9/5+32 so the routine should read

Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = (Target.Value * (9 / 5) + 32)

Tony

----- Pete Csiszar wrote: -----

Hi All,

I need a little guidance. I'm trying to create a Sub that converts
temperature from Fahraneit to Celcius and Celcius to Fahrenheit with
the
Worksheet_Change(ByVal Target As Excel.Range).
In my sheet I have set up K20 for inputing Deg F and M20 for inputing
Deg C.
When you change the value in K20 the corresponding Celcius value is
calculated in M20 and vise versa.

I have often used the following routine for conversions such as this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set isect = Intersect(Target, Range("K20"))
If Not isect Is Nothing Then
Range("M20").Value = ((Target.Value - 32) * (5 / 9))
End If
Set isect = Intersect(Target, Range("M20"))
If Not isect Is Nothing Then
Range("K20").Value = ((Target.Value + 32) * (9 / 5))
End If
End Sub

Usually I just multiply or divide the "Target.Value" by a single
integer but
with the scenario above the values in the cells runaway out of
control and
return some strange value.
Can someone help shed some light on this.
I'm open to any kind of alternate method of making the conversion
happen
when I enter a value into one cell or the other.

If more clarity is required please let me know.

TIA

Pete