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