How may actionable items can you have under an ElseIf Statement

[Martin888]

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

 

[FSt1]

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]

Hi,
thankyou for the suggestion unfortunatley the problem continues unabated
Cheers Martin888

 

[Jim Thomlinson]

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

 

[Martin888]

Good morning Jim
Many thanks code now running correctly
Cheers Martin