| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure too large error
I wrote some code that has become to large. I get a compile error: procedure
to large when I try to run it(from a button click). I need help re-writing it so its smaller. Here is the code, this example is for just one employee, i have to do this for 9 employees in total, that's why it gets to big. Private Sub CommandButton7_Click() €˜employee #1 'logged in time If Range("BF2").Text = "Employee #1" Then Range("BP19").Value = 1 Else If Range("BF3").Text = "Employee #1" Then Range("BP19").Value = 2 Else If Range("BF4").Text = "Employee #1" Then Range("BP19").Value = 3 Else If Range("BF5").Text = "Employee #1" Then Range("BP19").Value = 4 Else If Range("BF6").Text = "Employee #1" Then Range("BP19").Value = 5 Else If Range("BF7").Text = "Employee #1" Then Range("BP19").Value = 6 Else If Range("BF8").Text = "Employee #1" Then Range("BP19").Value = 7 Else If Range("BF9").Text = "Employee #1" Then Range("BP19").Value = 8 Else If Range("BF10").Text = "Employee #1" Then Range("BP19").Value = 9 Else 'availability If Range("BJ2").Text = "Employee #1" Then Range("BQ19").Value = 1 Else If Range("BJ3").Text = "Employee #1" Then Range("BQ19").Value = 2 Else If Range("BJ4").Text = "Employee #1" Then Range("BQ19").Value = 3 Else If Range("BJ5").Text = "Employee #1" Then Range("BQ19").Value = 4 Else If Range("BJ6").Text = "Employee #1" Then Range("BQ19").Value = 5 Else If Range("BJ7").Text = "Employee #1" Then Range("BQ19").Value = 6 Else If Range("BJ8").Text = "Employee #1" Then Range("BQ19").Value = 7 Else If Range("BJ9").Text = "Employee #1" Then Range("BQ19").Value = 8 Else If Range("BJ10").Text = "Employee #1" Then Range("BQ19").Value = 9 Else 'calls presented If Range("BB19").Text = "Employee #1" Then Range("BR19").Value = 1 Else If Range("BB20").Text = "Employee #1" Then Range("BR19").Value = 2 Else If Range("BB21").Text = "Employee #1" Then Range("BR19").Value = 3 Else If Range("BB22").Text = "Employee #1" Then Range("BR19").Value = 4 Else If Range("BB23").Text = "Employee #1" Then Range("BR19").Value = 5 Else If Range("BB24").Text = "Employee #1" Then Range("BR19").Value = 6 Else If Range("BB25").Text = "Employee #1" Then Range("BR19").Value = 7 Else If Range("BB26").Text = "Employee #1" Then Range("BR19").Value = 8 Else If Range("BB27").Text = "Employee #1" Then Range("BR19").Value = 9 Else 'calls answered If Range("BF19").Text = "Employee #1" Then Range("BS19").Value = 1 Else If Range("BF20").Text = "Employee #1" Then Range("BS19").Value = 2 Else If Range("BF21").Text = "Employee #1" Then Range("BS19").Value = 3 Else If Range("BF22").Text = "Employee #1" Then Range("BS19").Value = 4 Else If Range("BF23").Text = "Employee #1" Then Range("BS19").Value = 5 Else If Range("BF24").Text = "Employee #1" Then Range("BS19").Value = 6 Else If Range("BF25").Text = "Employee #1" Then Range("BS19").Value = 7 Else If Range("BF26").Text = "Employee #1" Then Range("BS19").Value = 8 Else If Range("BF27").Text = "Employee #1" Then Range("BS19").Value = 9 Else 'work share If Range("BJ19").Text = "Employee #1" Then Range("BT19").Value = 1 Else If Range("BJ20").Text = "Employee #1" Then Range("BT19").Value = 2 Else If Range("BJ21").Text = "Employee #1" Then Range("BT19").Value = 3 Else If Range("BJ22").Text = "Employee #1" Then Range("BT19").Value = 4 Else If Range("BJ23").Text = "Employee #1" Then Range("BT19").Value = 5 Else If Range("BJ24").Text = "Employee #1" Then Range("BT19").Value = 6 Else If Range("BJ25").Text = "Employee #1" Then Range("BT19").Value = 7 Else If Range("BJ26").Text = "Employee #1" Then Range("BT19").Value = 8 Else If Range("BJ27").Text = "Employee #1" Then Range("BT19").Value = 9 Else 'talk time If Range("BB36").Text = "Employee #1" Then Range("BU19").Value = 1 Else If Range("BB37").Text = "Employee #1" Then Range("BU19").Value = 2 Else If Range("BB38").Text = "Employee #1" Then Range("BU19").Value = 3 Else If Range("BB39").Text = "Employee #1" Then Range("BU19").Value = 4 Else If Range("BB40").Text = "Employee #1" Then Range("BU19").Value = 5 Else If Range("BB41").Text = "Employee #1" Then Range("BU19").Value = 6 Else If Range("BB42").Text = "Employee #1" Then Range("BU19").Value = 7 Else If Range("BB43").Text = "Employee #1" Then Range("BU19").Value = 8 Else If Range("BB44").Text = "Employee #1" Then Range("BU19").Value = 9 Else 'not ready time If Range("BF36").Text = "Employee #1" Then Range("BV19").Value = 1 Else If Range("BF37").Text = "Employee #1" Then Range("BV19").Value = 2 Else If Range("BF38").Text = "Employee #1" Then Range("BV19").Value = 3 Else If Range("BF39").Text = "Employee #1" Then Range("BV19").Value = 4 Else If Range("BF40").Text = "Employee #1" Then Range("BV19").Value = 5 Else If Range("BF41").Text = "Employee #1" Then Range("BV19").Value = 6 Else If Range("BF42").Text = "Employee #1" Then Range("BV19").Value = 7 Else If Range("BF43").Text = "Employee #1" Then Range("BV19").Value = 8 Else If Range("BF44").Text = "Employee #1" Then Range("BV19").Value = 9 Else 'tickets created If Range("BB53").Text = "Employee #1" Then Range("BW19").Value = 1 Else If Range("BB54").Text = "Employee #1" Then Range("BW19").Value = 2 Else If Range("BB55").Text = "Employee #1" Then Range("BW19").Value = 3 Else If Range("BB56").Text = "Employee #1" Then Range("BW19").Value = 4 Else If Range("BB57").Text = "Employee #1" Then Range("BW19").Value = 5 Else If Range("BB58").Text = "Employee #1" Then Range("BW19").Value = 6 Else If Range("BB59").Text = "Employee #1" Then Range("BW19").Value = 7 Else If Range("BB60").Text = "Employee #1" Then Range("BW19").Value = 8 Else If Range("BB61").Text = "Employee #1" Then Range("BW19").Value = 9 Else 'tickets closed If Range("BF53").Text = "Employee #1" Then Range("BX19").Value = 1 Else If Range("BF54").Text = "Employee #1" Then Range("BX19").Value = 2 Else If Range("BF55").Text = "Employee #1" Then Range("BX19").Value = 3 Else If Range("BF56").Text = "Employee #1" Then Range("BX19").Value = 4 Else If Range("BF57").Text = "Employee #1" Then Range("BX19").Value = 5 Else If Range("BF58").Text = "Employee #1" Then Range("BX19").Value = 6 Else If Range("BF59").Text = "Employee #1" Then Range("BX19").Value = 7 Else If Range("BF60").Text = "Employee #1" Then Range("BX19").Value = 8 Else If Range("BF61").Text = "Employee #1" Then Range("BX19").Value = 9 Else 'resolution If Range("BJ53").Text = "Employee #1" Then Range("BY19").Value = 1 Else If Range("BJ54").Text = "Employee #1" Then Range("BY19").Value = 2 Else If Range("BJ55").Text = "Employee #1" Then Range("BY19").Value = 3 Else If Range("BJ56").Text = "Employee #1" Then Range("BY19").Value = 4 Else If Range("BJ57").Text = "Employee #1" Then Range("BY19").Value = 5 Else If Range("BJ58").Text = "Employee #1" Then Range("BY19").Value = 6 Else If Range("BJ59").Text = "Employee #1" Then Range("BY19").Value = 7 Else If Range("BJ60").Text = "Employee #1" Then Range("BY19").Value = 8 Else If Range("BJ61").Text = "Employee #1" Then Range("BY19").Value = 9 Else End Sub Thanks for any help you can provide, Phil |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large error
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large error
Hi Phil
Use a seperate subroutine for the repetitive stuff: Option Explicit Private Sub checkcells(szEmp As String, lrowStart As Long, lRowEnd As Long, szCol As String, szDestCell As String) Dim lRow As Long Dim ans As Long ans = 9 For lRow = lRowEnd To lrowStart Step -1 ' work backwards If Cells(lRow, szCol) = szEmp Then ' if found it set the cell and exit ans no more checks. Range(szDestCell).Value = ans Exit Sub End If ans = ans - 1 End Sub Private Sub CommandButton7_Click() Const cszEmp As String = "Employee #1" 'employee #1 'logged in time checkcells cszEmp, 2, 10, "BF", "BP19" 'availability checkcells cszEmp, 2, 10, "BJ", "BQ19" 'calls presented checkcells cszEmp, 19, 27, "BB", "BR19" 'calls answered checkcells cszEmp, 19, 27, "BF", "BS19" 'work share checkcells cszEmp, 19, 27, "BJ", "BT19" 'talk time checkcells cszEmp, 36, 44, "BB", "BU19" 'not ready time checkcells cszEmp, 36, 44, "BF", "BV19" 'tickets created checkcells cszEmp, 53, 61, "BB", "BW19" 'tickets closed checkcells cszEmp, 53, 61, "BF", "BX19" 'resolution checkcells cszEmp, 53, 61, "BJ", "BY19" End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "phil-rge-ee" wrote: I wrote some code that has become to large. I get a compile error: procedure to large when I try to run it(from a button click). I need help re-writing it so its smaller. Here is the code, this example is for just one employee, i have to do this for 9 employees in total, that's why it gets to big. Private Sub CommandButton7_Click() €˜employee #1 'logged in time If Range("BF2").Text = "Employee #1" Then Range("BP19").Value = 1 Else If Range("BF3").Text = "Employee #1" Then Range("BP19").Value = 2 Else If Range("BF4").Text = "Employee #1" Then Range("BP19").Value = 3 Else If Range("BF5").Text = "Employee #1" Then Range("BP19").Value = 4 Else If Range("BF6").Text = "Employee #1" Then Range("BP19").Value = 5 Else If Range("BF7").Text = "Employee #1" Then Range("BP19").Value = 6 Else If Range("BF8").Text = "Employee #1" Then Range("BP19").Value = 7 Else If Range("BF9").Text = "Employee #1" Then Range("BP19").Value = 8 Else If Range("BF10").Text = "Employee #1" Then Range("BP19").Value = 9 Else 'availability If Range("BJ2").Text = "Employee #1" Then Range("BQ19").Value = 1 Else If Range("BJ3").Text = "Employee #1" Then Range("BQ19").Value = 2 Else If Range("BJ4").Text = "Employee #1" Then Range("BQ19").Value = 3 Else If Range("BJ5").Text = "Employee #1" Then Range("BQ19").Value = 4 Else If Range("BJ6").Text = "Employee #1" Then Range("BQ19").Value = 5 Else If Range("BJ7").Text = "Employee #1" Then Range("BQ19").Value = 6 Else If Range("BJ8").Text = "Employee #1" Then Range("BQ19").Value = 7 Else If Range("BJ9").Text = "Employee #1" Then Range("BQ19").Value = 8 Else If Range("BJ10").Text = "Employee #1" Then Range("BQ19").Value = 9 Else 'calls presented If Range("BB19").Text = "Employee #1" Then Range("BR19").Value = 1 Else If Range("BB20").Text = "Employee #1" Then Range("BR19").Value = 2 Else If Range("BB21").Text = "Employee #1" Then Range("BR19").Value = 3 Else If Range("BB22").Text = "Employee #1" Then Range("BR19").Value = 4 Else If Range("BB23").Text = "Employee #1" Then Range("BR19").Value = 5 Else If Range("BB24").Text = "Employee #1" Then Range("BR19").Value = 6 Else If Range("BB25").Text = "Employee #1" Then Range("BR19").Value = 7 Else If Range("BB26").Text = "Employee #1" Then Range("BR19").Value = 8 Else If Range("BB27").Text = "Employee #1" Then Range("BR19").Value = 9 Else 'calls answered If Range("BF19").Text = "Employee #1" Then Range("BS19").Value = 1 Else If Range("BF20").Text = "Employee #1" Then Range("BS19").Value = 2 Else If Range("BF21").Text = "Employee #1" Then Range("BS19").Value = 3 Else If Range("BF22").Text = "Employee #1" Then Range("BS19").Value = 4 Else If Range("BF23").Text = "Employee #1" Then Range("BS19").Value = 5 Else If Range("BF24").Text = "Employee #1" Then Range("BS19").Value = 6 Else If Range("BF25").Text = "Employee #1" Then Range("BS19").Value = 7 Else If Range("BF26").Text = "Employee #1" Then Range("BS19").Value = 8 Else If Range("BF27").Text = "Employee #1" Then Range("BS19").Value = 9 Else 'work share If Range("BJ19").Text = "Employee #1" Then Range("BT19").Value = 1 Else If Range("BJ20").Text = "Employee #1" Then Range("BT19").Value = 2 Else If Range("BJ21").Text = "Employee #1" Then Range("BT19").Value = 3 Else If Range("BJ22").Text = "Employee #1" Then Range("BT19").Value = 4 Else If Range("BJ23").Text = "Employee #1" Then Range("BT19").Value = 5 Else If Range("BJ24").Text = "Employee #1" Then Range("BT19").Value = 6 Else If Range("BJ25").Text = "Employee #1" Then Range("BT19").Value = 7 Else If Range("BJ26").Text = "Employee #1" Then Range("BT19").Value = 8 Else If Range("BJ27").Text = "Employee #1" Then Range("BT19").Value = 9 Else 'talk time If Range("BB36").Text = "Employee #1" Then Range("BU19").Value = 1 Else If Range("BB37").Text = "Employee #1" Then Range("BU19").Value = 2 Else If Range("BB38").Text = "Employee #1" Then Range("BU19").Value = 3 Else If Range("BB39").Text = "Employee #1" Then Range("BU19").Value = 4 Else If Range("BB40").Text = "Employee #1" Then Range("BU19").Value = 5 Else If Range("BB41").Text = "Employee #1" Then Range("BU19").Value = 6 Else If Range("BB42").Text = "Employee #1" Then Range("BU19").Value = 7 Else If Range("BB43").Text = "Employee #1" Then Range("BU19").Value = 8 Else If Range("BB44").Text = "Employee #1" Then Range("BU19").Value = 9 Else 'not ready time If Range("BF36").Text = "Employee #1" Then Range("BV19").Value = 1 Else If Range("BF37").Text = "Employee #1" Then Range("BV19").Value = 2 Else If Range("BF38").Text = "Employee #1" Then Range("BV19").Value = 3 Else If Range("BF39").Text = "Employee #1" Then Range("BV19").Value = 4 Else If Range("BF40").Text = "Employee #1" Then Range("BV19").Value = 5 Else If Range("BF41").Text = "Employee #1" Then Range("BV19").Value = 6 Else If Range("BF42").Text = "Employee #1" Then Range("BV19").Value = 7 Else If Range("BF43").Text = "Employee #1" Then Range("BV19").Value = 8 Else If Range("BF44").Text = "Employee #1" Then Range("BV19").Value = 9 Else 'tickets created If Range("BB53").Text = "Employee #1" Then Range("BW19").Value = 1 Else If Range("BB54").Text = "Employee #1" Then Range("BW19").Value = 2 Else If Range("BB55").Text = "Employee #1" Then Range("BW19").Value = 3 Else If Range("BB56").Text = "Employee #1" Then Range("BW19").Value = 4 Else If Range("BB57").Text = "Employee #1" Then Range("BW19").Value = 5 Else If Range("BB58").Text = "Employee #1" Then Range("BW19").Value = 6 Else If Range("BB59").Text = "Employee #1" Then Range("BW19").Value = 7 Else If Range("BB60").Text = "Employee #1" Then Range("BW19").Value = 8 Else If Range("BB61").Text = "Employee #1" Then Range("BW19").Value = 9 Else 'tickets closed If Range("BF53").Text = "Employee #1" Then Range("BX19").Value = 1 Else If Range("BF54").Text = "Employee #1" Then Range("BX19").Value = 2 Else If Range("BF55").Text = "Employee #1" Then Range("BX19").Value = 3 Else If Range("BF56").Text = "Employee #1" Then Range("BX19").Value = 4 Else If Range("BF57").Text = "Employee #1" Then Range("BX19").Value = 5 Else If Range("BF58").Text = "Employee #1" Then Range("BX19").Value = 6 Else If Range("BF59").Text = "Employee #1" Then Range("BX19").Value = 7 Else If Range("BF60").Text = "Employee #1" Then Range("BX19").Value = 8 Else If Range("BF61").Text = "Employee #1" Then Range("BX19").Value = 9 Else 'resolution If Range("BJ53").Text = "Employee #1" Then Range("BY19").Value = 1 Else If Range("BJ54").Text = "Employee #1" Then Range("BY19").Value = 2 Else If Range("BJ55").Text = "Employee #1" Then Range("BY19").Value = 3 Else If Range("BJ56").Text = "Employee #1" Then Range("BY19").Value = 4 Else If Range("BJ57").Text = "Employee #1" Then Range("BY19").Value = 5 Else If Range("BJ58").Text = "Employee #1" Then Range("BY19").Value = 6 Else If Range("BJ59").Text = "Employee #1" Then Range("BY19").Value = 7 Else If Range("BJ60").Text = "Employee #1" Then Range("BY19").Value = 8 Else If Range("BJ61").Text = "Employee #1" Then Range("BY19").Value = 9 Else End Sub Thanks for any help you can provide, Phil |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large error
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large error
Slight omission in the code on the first sub need a next....:
Private Sub checkcells(szEmp As String, lrowStart As Long, lRowEnd As Long, szCol As String, szDestCell As String) Dim lRow As Long Dim ans As Long ans = 9 For lRow = lRowEnd To lrowStart Step -1 ' work backwards If Cells(lRow, szCol) = szEmp Then ' if found it set the cell and exit ans no more checks. Range(szDestCell).Value = ans Exit Sub End If ans = ans - 1 Next lRow ' <<<<<<<<<<<<<<< End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Martin Fishlock" wrote: Hi Phil Use a seperate subroutine for the repetitive stuff: Option Explicit Private Sub checkcells(szEmp As String, lrowStart As Long, lRowEnd As Long, szCol As String, szDestCell As String) Dim lRow As Long Dim ans As Long ans = 9 For lRow = lRowEnd To lrowStart Step -1 ' work backwards If Cells(lRow, szCol) = szEmp Then ' if found it set the cell and exit ans no more checks. Range(szDestCell).Value = ans Exit Sub End If ans = ans - 1 End Sub Private Sub CommandButton7_Click() Const cszEmp As String = "Employee #1" 'employee #1 'logged in time checkcells cszEmp, 2, 10, "BF", "BP19" 'availability checkcells cszEmp, 2, 10, "BJ", "BQ19" 'calls presented checkcells cszEmp, 19, 27, "BB", "BR19" 'calls answered checkcells cszEmp, 19, 27, "BF", "BS19" 'work share checkcells cszEmp, 19, 27, "BJ", "BT19" 'talk time checkcells cszEmp, 36, 44, "BB", "BU19" 'not ready time checkcells cszEmp, 36, 44, "BF", "BV19" 'tickets created checkcells cszEmp, 53, 61, "BB", "BW19" 'tickets closed checkcells cszEmp, 53, 61, "BF", "BX19" 'resolution checkcells cszEmp, 53, 61, "BJ", "BY19" End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "phil-rge-ee" wrote: I wrote some code that has become to large. I get a compile error: procedure to large when I try to run it(from a button click). I need help re-writing it so its smaller. Here is the code, this example is for just one employee, i have to do this for 9 employees in total, that's why it gets to big. Private Sub CommandButton7_Click() €˜employee #1 'logged in time If Range("BF2").Text = "Employee #1" Then Range("BP19").Value = 1 Else If Range("BF3").Text = "Employee #1" Then Range("BP19").Value = 2 Else If Range("BF4").Text = "Employee #1" Then Range("BP19").Value = 3 Else If Range("BF5").Text = "Employee #1" Then Range("BP19").Value = 4 Else If Range("BF6").Text = "Employee #1" Then Range("BP19").Value = 5 Else If Range("BF7").Text = "Employee #1" Then Range("BP19").Value = 6 Else If Range("BF8").Text = "Employee #1" Then Range("BP19").Value = 7 Else If Range("BF9").Text = "Employee #1" Then Range("BP19").Value = 8 Else If Range("BF10").Text = "Employee #1" Then Range("BP19").Value = 9 Else 'availability If Range("BJ2").Text = "Employee #1" Then Range("BQ19").Value = 1 Else If Range("BJ3").Text = "Employee #1" Then Range("BQ19").Value = 2 Else If Range("BJ4").Text = "Employee #1" Then Range("BQ19").Value = 3 Else If Range("BJ5").Text = "Employee #1" Then Range("BQ19").Value = 4 Else If Range("BJ6").Text = "Employee #1" Then Range("BQ19").Value = 5 Else If Range("BJ7").Text = "Employee #1" Then Range("BQ19").Value = 6 Else If Range("BJ8").Text = "Employee #1" Then Range("BQ19").Value = 7 Else If Range("BJ9").Text = "Employee #1" Then Range("BQ19").Value = 8 Else If Range("BJ10").Text = "Employee #1" Then Range("BQ19").Value = 9 Else 'calls presented If Range("BB19").Text = "Employee #1" Then Range("BR19").Value = 1 Else If Range("BB20").Text = "Employee #1" Then Range("BR19").Value = 2 Else If Range("BB21").Text = "Employee #1" Then Range("BR19").Value = 3 Else If Range("BB22").Text = "Employee #1" Then Range("BR19").Value = 4 Else If Range("BB23").Text = "Employee #1" Then Range("BR19").Value = 5 Else If Range("BB24").Text = "Employee #1" Then Range("BR19").Value = 6 Else If Range("BB25").Text = "Employee #1" Then Range("BR19").Value = 7 Else If Range("BB26").Text = "Employee #1" Then Range("BR19").Value = 8 Else If Range("BB27").Text = "Employee #1" Then Range("BR19").Value = 9 Else 'calls answered If Range("BF19").Text = "Employee #1" Then Range("BS19").Value = 1 Else If Range("BF20").Text = "Employee #1" Then Range("BS19").Value = 2 Else If Range("BF21").Text = "Employee #1" Then Range("BS19").Value = 3 Else If Range("BF22").Text = "Employee #1" Then Range("BS19").Value = 4 Else If Range("BF23").Text = "Employee #1" Then Range("BS19").Value = 5 Else If Range("BF24").Text = "Employee #1" Then Range("BS19").Value = 6 Else If Range("BF25").Text = "Employee #1" Then Range("BS19").Value = 7 Else If Range("BF26").Text = "Employee #1" Then Range("BS19").Value = 8 Else If Range("BF27").Text = "Employee #1" Then Range("BS19").Value = 9 Else 'work share If Range("BJ19").Text = "Employee #1" Then Range("BT19").Value = 1 Else If Range("BJ20").Text = "Employee #1" Then Range("BT19").Value = 2 Else If Range("BJ21").Text = "Employee #1" Then Range("BT19").Value = 3 Else If Range("BJ22").Text = "Employee #1" Then Range("BT19").Value = 4 Else If Range("BJ23").Text = "Employee #1" Then Range("BT19").Value = 5 Else If Range("BJ24").Text = "Employee #1" Then Range("BT19").Value = 6 Else If Range("BJ25").Text = "Employee #1" Then Range("BT19").Value = 7 Else If Range("BJ26").Text = "Employee #1" Then Range("BT19").Value = 8 Else If Range("BJ27").Text = "Employee #1" Then Range("BT19").Value = 9 Else 'talk time If Range("BB36").Text = "Employee #1" Then Range("BU19").Value = 1 Else If Range("BB37").Text = "Employee #1" Then Range("BU19").Value = 2 Else If Range("BB38").Text = "Employee #1" Then Range("BU19").Value = 3 Else If Range("BB39").Text = "Employee #1" Then Range("BU19").Value = 4 Else If Range("BB40").Text = "Employee #1" Then Range("BU19").Value = 5 Else If Range("BB41").Text = "Employee #1" Then Range("BU19").Value = 6 Else If Range("BB42").Text = "Employee #1" Then Range("BU19").Value = 7 Else If Range("BB43").Text = "Employee #1" Then Range("BU19").Value = 8 Else If Range("BB44").Text = "Employee #1" Then Range("BU19").Value = 9 Else 'not ready time If Range("BF36").Text = "Employee #1" Then Range("BV19").Value = 1 Else If Range("BF37").Text = "Employee #1" Then Range("BV19").Value = 2 Else If Range("BF38").Text = "Employee #1" Then Range("BV19").Value = 3 Else If Range("BF39").Text = "Employee #1" Then Range("BV19").Value = 4 Else If Range("BF40").Text = "Employee #1" Then Range("BV19").Value = 5 Else If Range("BF41").Text = "Employee #1" Then Range("BV19").Value = 6 Else If Range("BF42").Text = "Employee #1" Then Range("BV19").Value = 7 Else If Range("BF43").Text = "Employee #1" Then Range("BV19").Value = 8 Else If Range("BF44").Text = "Employee #1" Then Range("BV19").Value = 9 Else 'tickets created If Range("BB53").Text = "Employee #1" Then Range("BW19").Value = 1 Else If Range("BB54").Text = "Employee #1" Then Range("BW19").Value = 2 Else If Range("BB55").Text = "Employee #1" Then Range("BW19").Value = 3 Else If Range("BB56").Text = "Employee #1" Then Range("BW19").Value = 4 Else If Range("BB57").Text = "Employee #1" Then Range("BW19").Value = 5 Else If Range("BB58").Text = "Employee #1" Then Range("BW19").Value = 6 Else If Range("BB59").Text = "Employee #1" Then Range("BW19").Value = 7 Else If Range("BB60").Text = "Employee #1" Then Range("BW19").Value = 8 Else If Range("BB61").Text = "Employee #1" Then Range("BW19").Value = 9 Else 'tickets closed If Range("BF53").Text = "Employee #1" Then Range("BX19").Value = 1 Else If Range("BF54").Text = "Employee #1" Then Range("BX19").Value = 2 Else If Range("BF55").Text = "Employee #1" Then Range("BX19").Value = 3 Else If Range("BF56").Text = "Employee #1" Then Range("BX19").Value = 4 Else If Range("BF57").Text = "Employee #1" Then Range("BX19").Value = 5 Else If Range("BF58").Text = "Employee #1" Then Range("BX19").Value = 6 Else If Range("BF59").Text = "Employee #1" Then Range("BX19").Value = 7 Else If Range("BF60").Text = "Employee #1" Then Range("BX19").Value = 8 Else If Range("BF61").Text = "Employee #1" Then Range("BX19").Value = 9 Else 'resolution If Range("BJ53").Text = "Employee #1" Then Range("BY19").Value = 1 Else If Range("BJ54").Text = "Employee #1" Then Range("BY19").Value = 2 Else If Range("BJ55").Text = "Employee #1" Then Range("BY19").Value = 3 Else If Range("BJ56").Text = "Employee #1" Then Range("BY19").Value = 4 Else If Range("BJ57").Text = "Employee #1" Then Range("BY19").Value = 5 Else If Range("BJ58").Text = "Employee #1" Then Range("BY19").Value = 6 Else If Range("BJ59").Text = "Employee #1" Then Range("BY19").Value = 7 Else If Range("BJ60").Text = "Employee #1" Then Range("BY19").Value = 8 Else If Range("BJ61").Text = "Employee #1" Then Range("BY19").Value = 9 Else End Sub Thanks for any help you can provide, Phil |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large error
Thanks alot, I give it a try.
Phil "Martin Fishlock" wrote: Slight omission in the code on the first sub need a next....: Private Sub checkcells(szEmp As String, lrowStart As Long, lRowEnd As Long, szCol As String, szDestCell As String) Dim lRow As Long Dim ans As Long ans = 9 For lRow = lRowEnd To lrowStart Step -1 ' work backwards If Cells(lRow, szCol) = szEmp Then ' if found it set the cell and exit ans no more checks. Range(szDestCell).Value = ans Exit Sub End If ans = ans - 1 Next lRow ' <<<<<<<<<<<<<<< End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Martin Fishlock" wrote: Hi Phil Use a seperate subroutine for the repetitive stuff: Option Explicit Private Sub checkcells(szEmp As String, lrowStart As Long, lRowEnd As Long, szCol As String, szDestCell As String) Dim lRow As Long Dim ans As Long ans = 9 For lRow = lRowEnd To lrowStart Step -1 ' work backwards If Cells(lRow, szCol) = szEmp Then ' if found it set the cell and exit ans no more checks. Range(szDestCell).Value = ans Exit Sub End If ans = ans - 1 End Sub Private Sub CommandButton7_Click() Const cszEmp As String = "Employee #1" 'employee #1 'logged in time checkcells cszEmp, 2, 10, "BF", "BP19" 'availability checkcells cszEmp, 2, 10, "BJ", "BQ19" 'calls presented checkcells cszEmp, 19, 27, "BB", "BR19" 'calls answered checkcells cszEmp, 19, 27, "BF", "BS19" 'work share checkcells cszEmp, 19, 27, "BJ", "BT19" 'talk time checkcells cszEmp, 36, 44, "BB", "BU19" 'not ready time checkcells cszEmp, 36, 44, "BF", "BV19" 'tickets created checkcells cszEmp, 53, 61, "BB", "BW19" 'tickets closed checkcells cszEmp, 53, 61, "BF", "BX19" 'resolution checkcells cszEmp, 53, 61, "BJ", "BY19" End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "phil-rge-ee" wrote: I wrote some code that has become to large. I get a compile error: procedure to large when I try to run it(from a button click). I need help re-writing it so its smaller. Here is the code, this example is for just one employee, i have to do this for 9 employees in total, that's why it gets to big. Private Sub CommandButton7_Click() €˜employee #1 'logged in time If Range("BF2").Text = "Employee #1" Then Range("BP19").Value = 1 Else If Range("BF3").Text = "Employee #1" Then Range("BP19").Value = 2 Else If Range("BF4").Text = "Employee #1" Then Range("BP19").Value = 3 Else If Range("BF5").Text = "Employee #1" Then Range("BP19").Value = 4 Else If Range("BF6").Text = "Employee #1" Then Range("BP19").Value = 5 Else If Range("BF7").Text = "Employee #1" Then Range("BP19").Value = 6 Else If Range("BF8").Text = "Employee #1" Then Range("BP19").Value = 7 Else If Range("BF9").Text = "Employee #1" Then Range("BP19").Value = 8 Else If Range("BF10").Text = "Employee #1" Then Range("BP19").Value = 9 Else 'availability If Range("BJ2").Text = "Employee #1" Then Range("BQ19").Value = 1 Else If Range("BJ3").Text = "Employee #1" Then Range("BQ19").Value = 2 Else If Range("BJ4").Text = "Employee #1" Then Range("BQ19").Value = 3 Else If Range("BJ5").Text = "Employee #1" Then Range("BQ19").Value = 4 Else If Range("BJ6").Text = "Employee #1" Then Range("BQ19").Value = 5 Else If Range("BJ7").Text = "Employee #1" Then Range("BQ19").Value = 6 Else If Range("BJ8").Text = "Employee #1" Then Range("BQ19").Value = 7 Else If Range("BJ9").Text = "Employee #1" Then Range("BQ19").Value = 8 Else If Range("BJ10").Text = "Employee #1" Then Range("BQ19").Value = 9 Else 'calls presented If Range("BB19").Text = "Employee #1" Then Range("BR19").Value = 1 Else If Range("BB20").Text = "Employee #1" Then Range("BR19").Value = 2 Else If Range("BB21").Text = "Employee #1" Then Range("BR19").Value = 3 Else If Range("BB22").Text = "Employee #1" Then Range("BR19").Value = 4 Else If Range("BB23").Text = "Employee #1" Then Range("BR19").Value = 5 Else If Range("BB24").Text = "Employee #1" Then Range("BR19").Value = 6 Else If Range("BB25").Text = "Employee #1" Then Range("BR19").Value = 7 Else If Range("BB26").Text = "Employee #1" Then Range("BR19").Value = 8 Else If Range("BB27").Text = "Employee #1" Then Range("BR19").Value = 9 Else 'calls answered If Range("BF19").Text = "Employee #1" Then Range("BS19").Value = 1 Else If Range("BF20").Text = "Employee #1" Then Range("BS19").Value = 2 Else If Range("BF21").Text = "Employee #1" Then Range("BS19").Value = 3 Else If Range("BF22").Text = "Employee #1" Then Range("BS19").Value = 4 Else If Range("BF23").Text = "Employee #1" Then Range("BS19").Value = 5 Else If Range("BF24").Text = "Employee #1" Then Range("BS19").Value = 6 Else If Range("BF25").Text = "Employee #1" Then Range("BS19").Value = 7 Else If Range("BF26").Text = "Employee #1" Then Range("BS19").Value = 8 Else If Range("BF27").Text = "Employee #1" Then Range("BS19").Value = 9 Else 'work share If Range("BJ19").Text = "Employee #1" Then Range("BT19").Value = 1 Else If Range("BJ20").Text = "Employee #1" Then Range("BT19").Value = 2 Else If Range("BJ21").Text = "Employee #1" Then Range("BT19").Value = 3 Else If Range("BJ22").Text = "Employee #1" Then Range("BT19").Value = 4 Else If Range("BJ23").Text = "Employee #1" Then Range("BT19").Value = 5 Else If Range("BJ24").Text = "Employee #1" Then Range("BT19").Value = 6 Else If Range("BJ25").Text = "Employee #1" Then Range("BT19").Value = 7 Else If Range("BJ26").Text = "Employee #1" Then Range("BT19").Value = 8 Else If Range("BJ27").Text = "Employee #1" Then Range("BT19").Value = 9 Else 'talk time If Range("BB36").Text = "Employee #1" Then Range("BU19").Value = 1 Else If Range("BB37").Text = "Employee #1" Then Range("BU19").Value = 2 Else If Range("BB38").Text = "Employee #1" Then Range("BU19").Value = 3 Else If Range("BB39").Text = "Employee #1" Then Range("BU19").Value = 4 Else If Range("BB40").Text = "Employee #1" Then Range("BU19").Value = 5 Else If Range("BB41").Text = "Employee #1" Then Range("BU19").Value = 6 Else If Range("BB42").Text = "Employee #1" Then Range("BU19").Value = 7 Else If Range("BB43").Text = "Employee #1" Then Range("BU19").Value = 8 Else If Range("BB44").Text = "Employee #1" Then Range("BU19").Value = 9 Else 'not ready time If Range("BF36").Text = "Employee #1" Then Range("BV19").Value = 1 Else If Range("BF37").Text = "Employee #1" Then Range("BV19").Value = 2 Else If Range("BF38").Text = "Employee #1" Then Range("BV19").Value = 3 Else If Range("BF39").Text = "Employee #1" Then Range("BV19").Value = 4 Else If Range("BF40").Text = "Employee #1" Then Range("BV19").Value = 5 Else If Range("BF41").Text = "Employee #1" Then Range("BV19").Value = 6 Else If Range("BF42").Text = "Employee #1" Then Range("BV19").Value = 7 Else If Range("BF43").Text = "Employee #1" Then Range("BV19").Value = 8 Else If Range("BF44").Text = "Employee #1" Then Range("BV19").Value = 9 Else 'tickets created If Range("BB53").Text = "Employee #1" Then Range("BW19").Value = 1 Else If Range("BB54").Text = "Employee #1" Then Range("BW19").Value = 2 Else If Range("BB55").Text = "Employee #1" Then Range("BW19").Value = 3 Else If Range("BB56").Text = "Employee #1" Then Range("BW19").Value = 4 Else If Range("BB57").Text = "Employee #1" Then Range("BW19").Value = 5 Else If Range("BB58").Text = "Employee #1" Then Range("BW19").Value = 6 Else If Range("BB59").Text = "Employee #1" Then Range("BW19").Value = 7 Else If Range("BB60").Text = "Employee #1" Then Range("BW19").Value = 8 Else If Range("BB61").Text = "Employee #1" Then Range("BW19").Value = 9 Else 'tickets closed If Range("BF53").Text = "Employee #1" Then Range("BX19").Value = 1 Else If Range("BF54").Text = "Employee #1" Then Range("BX19").Value = 2 Else If Range("BF55").Text = "Employee #1" Then Range("BX19").Value = 3 Else If Range("BF56").Text = "Employee #1" Then Range("BX19").Value = 4 Else If Range("BF57").Text = "Employee #1" Then Range("BX19").Value = 5 Else If Range("BF58").Text = "Employee #1" Then Range("BX19").Value = 6 Else If Range("BF59").Text = "Employee #1" Then Range("BX19").Value = 7 Else If Range("BF60").Text = "Employee #1" Then Range("BX19").Value = 8 Else If Range("BF61").Text = "Employee #1" Then Range("BX19").Value = 9 Else 'resolution If Range("BJ53").Text = "Employee #1" Then Range("BY19").Value = 1 Else If Range("BJ54").Text = "Employee #1" Then Range("BY19").Value = 2 Else If Range("BJ55").Text = "Employee #1" Then Range("BY19").Value = 3 Else If Range("BJ56").Text = "Employee #1" Then Range("BY19").Value = 4 Else If Range("BJ57").Text = "Employee #1" Then Range("BY19").Value = 5 Else If Range("BJ58").Text = "Employee #1" Then Range("BY19").Value = 6 Else If Range("BJ59").Text = "Employee #1" Then Range("BY19").Value = 7 Else If Range("BJ60").Text = "Employee #1" Then Range("BY19").Value = 8 Else If Range("BJ61").Text = "Employee #1" Then Range("BY19").Value = 9 Else End Sub Thanks for any help you can provide, Phil |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large error
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA Compile error: Procedure too large? | Excel Discussion (Misc queries) | |||
| Compile error: Procedure too large | Excel Programming | |||
| Compile Error: Procedure too Large Question | Excel Programming | |||
| Compile error: Procedure too large | Excel Programming | |||
| Compile Error: Procedure too large | Excel Programming | |||
Linear Mode
