|
How may actionable items can you have under an ElseIf Statement
The following code works but self interrupts and does not close and
consequently the worksheet freezes and is only released by pressing the Esc key. Any suggestions? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = Worksheets("Run1").Range("G18") Set curcell2 = Worksheets("Run1").Range("H18") On Error GoTo Endit If curcell <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.3 And curcell <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.7 And curcell <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If Exit Sub Endit: End Sub |
How may actionable items can you have under an ElseIf Statement
hi
you are missing the value property of the range object...... If curcell.value <= 0.3 Then Range("H18").value = 4 Range("M25").value = 6.7 Range("M26").vlaue = 25 ect.ect ect.ect Regards FSt1 "Martin888" wrote: The following code works but self interrupts and does not close and consequently the worksheet freezes and is only released by pressing the Esc key. Any suggestions? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = Worksheets("Run1").Range("G18") Set curcell2 = Worksheets("Run1").Range("H18") On Error GoTo Endit If curcell <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.3 And curcell <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.7 And curcell <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If Exit Sub Endit: End Sub |
How may actionable items can you have under an ElseIf Statemen
Hi,
thankyou for the suggestion unfortunatley the problem continues unabated Cheers Martin888 "FSt1" wrote: hi you are missing the value property of the range object...... If curcell.value <= 0.3 Then Range("H18").value = 4 Range("M25").value = 6.7 Range("M26").vlaue = 25 ect.ect ect.ect Regards FSt1 "Martin888" wrote: The following code works but self interrupts and does not close and consequently the worksheet freezes and is only released by pressing the Esc key. Any suggestions? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = Worksheets("Run1").Range("G18") Set curcell2 = Worksheets("Run1").Range("H18") On Error GoTo Endit If curcell <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.3 And curcell <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.7 And curcell <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If Exit Sub Endit: End Sub |
How may actionable items can you have under an ElseIf Statemen
As a best guess without actually running your code you are getting into a
recursive loop. In your change event you are changing values which will initiate a cnage event that will ... You need to disable events while you are making the changes... Note that I added in the .value at the end of curcell. While value is the default it never hurts to be explicit. Also throughout your code you have Range("??"). You are best off to explicitly declare which sheet they are referencing. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = w .Range("G18") Set curcell2 = w .Range("H18") On Error GoTo Endit application.enableevents = false If curcell.value <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell.value 0.3 And curcell.value <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell.value 0.7 And curcell.value <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell.value 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If application.enableevents = true Exit Sub Endit: application.enableevents = true End Sub -- HTH... Jim Thomlinson "Martin888" wrote: Hi, thankyou for the suggestion unfortunatley the problem continues unabated Cheers Martin888 "FSt1" wrote: hi you are missing the value property of the range object...... If curcell.value <= 0.3 Then Range("H18").value = 4 Range("M25").value = 6.7 Range("M26").vlaue = 25 ect.ect ect.ect Regards FSt1 "Martin888" wrote: The following code works but self interrupts and does not close and consequently the worksheet freezes and is only released by pressing the Esc key. Any suggestions? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = Worksheets("Run1").Range("G18") Set curcell2 = Worksheets("Run1").Range("H18") On Error GoTo Endit If curcell <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.3 And curcell <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.7 And curcell <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If Exit Sub Endit: End Sub |
How may actionable items can you have under an ElseIf Statemen
Good morning Jim
Many thanks code now running correctly Cheers Martin "Jim Thomlinson" wrote: As a best guess without actually running your code you are getting into a recursive loop. In your change event you are changing values which will initiate a cnage event that will ... You need to disable events while you are making the changes... Note that I added in the .value at the end of curcell. While value is the default it never hurts to be explicit. Also throughout your code you have Range("??"). You are best off to explicitly declare which sheet they are referencing. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = w .Range("G18") Set curcell2 = w .Range("H18") On Error GoTo Endit application.enableevents = false If curcell.value <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell.value 0.3 And curcell.value <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell.value 0.7 And curcell.value <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell.value 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If application.enableevents = true Exit Sub Endit: application.enableevents = true End Sub -- HTH... Jim Thomlinson "Martin888" wrote: Hi, thankyou for the suggestion unfortunatley the problem continues unabated Cheers Martin888 "FSt1" wrote: hi you are missing the value property of the range object...... If curcell.value <= 0.3 Then Range("H18").value = 4 Range("M25").value = 6.7 Range("M26").vlaue = 25 ect.ect ect.ect Regards FSt1 "Martin888" wrote: The following code works but self interrupts and does not close and consequently the worksheet freezes and is only released by pressing the Esc key. Any suggestions? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim w As Worksheet Set w = Worksheets("Run1") Set curcell = Worksheets("Run1").Range("G18") Set curcell2 = Worksheets("Run1").Range("H18") On Error GoTo Endit If curcell <= 0.3 Then Range("H18") = 4 Range("M25") = 6.7 Range("M26") = 25 Range("M27") = 75 Range("M28") = 93.3 Range("M29") = 0 Range("M30") = 0 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.3 And curcell <= 0.7 Then Range("H18") = 6 Range("M25") = 4.4 Range("M26") = 14.6 Range("M27") = 29.6 Range("M28") = 70.5 Range("M29") = 85.4 Range("M30") = 95.6 Range("M31") = 0 Range("M32") = 0 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 0.7 And curcell <= 1 Then Range("H18") = 8 Range("M25") = 3.2 Range("M26") = 10.8 Range("M27") = 19.4 Range("M28") = 32.3 Range("M29") = 67.7 Range("M30") = 80.6 Range("M31") = 89.5 Range("M32") = 96.8 Range("M33") = 0 Range("M34") = 0 Range("M35") = 0 Range("M36") = 0 ElseIf curcell 1 Then Range("H18") = 12 Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8 Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") = 35.6 And Range("M31") = 64.4 Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") = 88.2 And Range("M35") = 93.3 And Range("M36") = 97.9 End If Exit Sub Endit: End Sub |
| All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com