Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How may actionable items can you have under an ElseIf Statement
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
handle error in IF ELSEIF statement Sabosis Excel Worksheet Functions 1 April 30th 09 05:06 AM
How can I compare more than 7 items in a conditional statement? if'd out Setting up and Configuration of Excel 3 March 16th 07 09:01 AM
How to simplify If / ElseIf statement Ksu Excel Programming 2 October 11th 06 05:04 PM
How to simplify If / ElseIf statement Ksu Excel Programming 4 October 8th 06 09:26 AM
ElseIf Statement problem Erik K via OfficeKB.com[_2_] Excel Programming 5 December 23rd 05 12:51 PM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement