 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2007Procedure 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 Try this idea. Depending on your setup it could be even easier with a looping macro . The if line is a ONE liner so correct for word wrap. Add ranges within " " as desired. Sub findemployees() If Not Range("b2:b22,c2:c22,d31:d41").Find("emm1") Is Nothing Then Range("e1") = 22 If Not Range("b2:b22,c2:c22,d31:d41").Find("emm2") Is Nothing Then Range("e2") = 44 'etc End Sub  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I wrote some code that has become to large. I get a compile error: >procedure [quoted text clipped - 109 lines] > Thanks for any help you can provide, > Phil 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 SignatureHope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. > 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 [quoted text clipped - 107 lines] > Thanks for any help you can provide, > Phil 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) [quoted text clipped - 8 lines] > End If > ans = ans - 1 Next lRow ' <<<<<<<<<<<<<<< > End Sub SignatureHope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. > Hi Phil > [quoted text clipped - 152 lines] > > Thanks for any help you can provide, > > Phil Thanks alot, I give it a try. Phil > Slight omission in the code on the first sub need a next....: > [quoted text clipped - 169 lines] > > > Thanks for any help you can provide, > > > Phil I misread the request. Try this idea. Sub cellbasedonfind() On Error Resume Next Range("d1") = Range("b2:b22").Find("emm1").Row - 1 'etc End Sub SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I wrote some code that has become to large. I get a compile error: >procedure [quoted text clipped - 109 lines] > Thanks for any help you can provide, > Phil Thanks alot, I'll give it a try. Phil > I misread the request. Try this idea. > [quoted text clipped - 117 lines] > > Thanks for any help you can provide, > > Phil |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.