Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi threaded calculation (multi CPU) - impact on calculation spe | Excel Discussion (Misc queries) | |||
Why do I have a runaway cursor in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |