just_jon
Legend
- Joined
- Sep 3, 2002
- Messages
- 10,473
I have a workbook with an event macro attached to the first sheet. Works great until a second - any second - workbook is opened up. Then I get "Run-time error '9': Subscript out of range". The code is attached below, and any help will be greatly appreciated. I've lurked this board a while and found a bunch of nuggets. Hope I can return the favor someday.
Thank you - just_jon in huntsville
Event macro:<pre>
Private Sub Worksheet_Calculate()
' Turn off event handling while macro is running..
Application.EnableEvents = False
Dim Counter, Lotsize, First, Last, i, j, Pass As Integer
Dim b, s, Curve, Midpt As Variant
Dim Avgunit_02, Avgunit_03, Avgunit_04, Avgunit_05, Avgunit_06 As Variant
Dim Funitval_02, Funitval_03, Funitval_04, Funitval_05, Funitval_06 As Variant
Dim Unival_02, Unival_03, Unival_04, Unival_05, Unival_06 As Variant
' Set any existing curved labor to 0...
Worksheets("Instant Order").Cells(22, 5) = 0
Worksheets("Instant Order").Cells(22, 6) = 0
Worksheets("Instant Order").Cells(22, 7) = 0
Worksheets("Instant Order").Cells(22, = 0
Worksheets("Instant Order").Cells(22, 9) = 0
' If instant order qty< 31 terminate execution...
If (Worksheets("Instant Order").Cells(5, 5)<= 30) Then
GoTo Not_Abend
End If
' Negotiated starting values, assuming 1300 Eq. units already built...
Curve = 89.1
First = 1300
Last = 1300
' Assign Avg Unit labor values from baseline data...
Avgunit_02 = Worksheets("Instant Order").Cells(20, 5)
Avgunit_03 = Worksheets("Instant Order").Cells(20, 6)
Avgunit_04 = Worksheets("Instant Order").Cells(20, 7)
Avgunit_05 = Worksheets("Instant Order").Cells(20,
Avgunit_06 = Worksheets("Instant Order").Cells(20, 9)
' Begin calculations...
Begin_Calc:
Pass = Pass + 1
b = (Application.WorksheetFunction.Ln(Curve / 100)) / Application.WorksheetFunction.Ln(2)
Lotsize = (Last - First) + 1
' Calculate midpoint...
If Lotsize > 1000 Then
Midpt = (((Last + 0.5) ^ (1 + b) - (First - 0.5)) ^ (1 + b) / Lotsize / (1 + b)) ^ (1 / b)
ElseIf First = Last Then
Midpt = First
Else
s = 0
For i = First To Last
s = s + (i ^ b)
Next i
Midpt = (s / Lotsize) ^ (1 / b)
End If
' Calculate First Unit Values...
If (Pass = 1) Then
Funitval_02 = Avgunit_02 / Midpt ^ b
Funitval_03 = Avgunit_03 / Midpt ^ b
Funitval_04 = Avgunit_04 / Midpt ^ b
Funitval_05 = Avgunit_05 / Midpt ^ b
Funitval_06 = Avgunit_06 / Midpt ^ b
End If
' Get instant order fab sequence...
First = 1300 + 1
Last = 1300 + (Worksheets("Instant Order").Cells(5, 5) - 0)
Unival_02 = Funitval_02 * Midpt ^ b
Unival_03 = Funitval_03 * Midpt ^ b
Unival_04 = Funitval_04 * Midpt ^ b
Unival_05 = Funitval_05 * Midpt ^ b
Unival_06 = Funitval_06 * Midpt ^ b
If (Pass = 1) Then
GoTo Begin_Calc
Else
' Re-seed curved values here...
Worksheets("Instant Order").Cells(22, 5) = Unival_02
Worksheets("Instant Order").Cells(22, 6) = Unival_03
Worksheets("Instant Order").Cells(22, 7) = Unival_04
Worksheets("Instant Order").Cells(22, = Unival_05
Worksheets("Instant Order").Cells(22, 9) = Unival_06
GoTo Not_Abend
End If
Not_Abend:
' Restore event handling...
Application.EnableEvents = True
End Sub</pre>
This message was edited by just_jon on 2003-01-06 13:55
Thank you - just_jon in huntsville
Event macro:<pre>
Private Sub Worksheet_Calculate()
' Turn off event handling while macro is running..
Application.EnableEvents = False
Dim Counter, Lotsize, First, Last, i, j, Pass As Integer
Dim b, s, Curve, Midpt As Variant
Dim Avgunit_02, Avgunit_03, Avgunit_04, Avgunit_05, Avgunit_06 As Variant
Dim Funitval_02, Funitval_03, Funitval_04, Funitval_05, Funitval_06 As Variant
Dim Unival_02, Unival_03, Unival_04, Unival_05, Unival_06 As Variant
' Set any existing curved labor to 0...
Worksheets("Instant Order").Cells(22, 5) = 0
Worksheets("Instant Order").Cells(22, 6) = 0
Worksheets("Instant Order").Cells(22, 7) = 0
Worksheets("Instant Order").Cells(22, = 0
Worksheets("Instant Order").Cells(22, 9) = 0
' If instant order qty< 31 terminate execution...
If (Worksheets("Instant Order").Cells(5, 5)<= 30) Then
GoTo Not_Abend
End If
' Negotiated starting values, assuming 1300 Eq. units already built...
Curve = 89.1
First = 1300
Last = 1300
' Assign Avg Unit labor values from baseline data...
Avgunit_02 = Worksheets("Instant Order").Cells(20, 5)
Avgunit_03 = Worksheets("Instant Order").Cells(20, 6)
Avgunit_04 = Worksheets("Instant Order").Cells(20, 7)
Avgunit_05 = Worksheets("Instant Order").Cells(20,
Avgunit_06 = Worksheets("Instant Order").Cells(20, 9)
' Begin calculations...
Begin_Calc:
Pass = Pass + 1
b = (Application.WorksheetFunction.Ln(Curve / 100)) / Application.WorksheetFunction.Ln(2)
Lotsize = (Last - First) + 1
' Calculate midpoint...
If Lotsize > 1000 Then
Midpt = (((Last + 0.5) ^ (1 + b) - (First - 0.5)) ^ (1 + b) / Lotsize / (1 + b)) ^ (1 / b)
ElseIf First = Last Then
Midpt = First
Else
s = 0
For i = First To Last
s = s + (i ^ b)
Next i
Midpt = (s / Lotsize) ^ (1 / b)
End If
' Calculate First Unit Values...
If (Pass = 1) Then
Funitval_02 = Avgunit_02 / Midpt ^ b
Funitval_03 = Avgunit_03 / Midpt ^ b
Funitval_04 = Avgunit_04 / Midpt ^ b
Funitval_05 = Avgunit_05 / Midpt ^ b
Funitval_06 = Avgunit_06 / Midpt ^ b
End If
' Get instant order fab sequence...
First = 1300 + 1
Last = 1300 + (Worksheets("Instant Order").Cells(5, 5) - 0)
Unival_02 = Funitval_02 * Midpt ^ b
Unival_03 = Funitval_03 * Midpt ^ b
Unival_04 = Funitval_04 * Midpt ^ b
Unival_05 = Funitval_05 * Midpt ^ b
Unival_06 = Funitval_06 * Midpt ^ b
If (Pass = 1) Then
GoTo Begin_Calc
Else
' Re-seed curved values here...
Worksheets("Instant Order").Cells(22, 5) = Unival_02
Worksheets("Instant Order").Cells(22, 6) = Unival_03
Worksheets("Instant Order").Cells(22, 7) = Unival_04
Worksheets("Instant Order").Cells(22, = Unival_05
Worksheets("Instant Order").Cells(22, 9) = Unival_06
GoTo Not_Abend
End If
Not_Abend:
' Restore event handling...
Application.EnableEvents = True
End Sub</pre>
This message was edited by just_jon on 2003-01-06 13:55