Help With Runaway Calculation

  • Thread starter Thread starter Pete Csiszar
  • Start date Start date
P

Pete Csiszar

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
 
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
 
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
 
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
 
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
 
Thanks Tony,
It works like a charm.
It kills me how something so simple could hang me up.

Regards,
Pete

acw said:
Pete

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