Hello,

I have a list of employees on a schedule. I have a piece of code look
them up, sort them by start time, and then put them on a daily line up for
printing.. Problem is that it takes a long time on the work PC's (200mhz).
The problem seems to be after the array returns sorted. I cannot seem to
figure it out. Any ideas?

Thanks...

Sub Sunday(week)

Dim TheArray(60, 3) As Variant
Dim a, c, e, R
Dim Lunch, After, Dinner, Late As Boolean

a = 0

Range("A9:C49").ClearContents

' Create a new progress bar
Set sb = New clsProgressBar

' Display the progress bar
sb.Show constWait, vbNullString, 0

' Create the array.

For Each c In Range(week)

a = a + 1

TheArray(a, 1) = c.Value
TheArray(a, 2) = c.Cells(1, 2).Value
TheArray(a, 3) = c.Cells(1, -1).Value

Next

' Sort the Array and display the values in order.

BubbleSort TheArray

'Lag seems to start here...
R = 10

For a = 1 To UBound(TheArray)

e = TheArray(a, 1)

If e = "" Then GoTo Bottom
If Application.WorksheetFunction.IsText(e) = True Then GoTo Bottom

If Lunch = False Then
If e = 0.458 Then
R = R + 1
Lunch = True
End If
End If

If After = False Then
If e = 0.58 Then
R = R + 7
After = True
End If
End If

If Dinner = False Then
If e = 0.708 Then
R = R + 1
Dinner = True
End If
End If

If Late = False Then
If e = 0.833 Then
R = R + 1
Late = True
End If
End If

Range("A" & R).Value = TheArray(a, 1)
Range("B" & R).Value = TheArray(a, 2)
Range("C" & R).Value = TheArray(a, 3)

R = R + 1

Bottom:
sb.PercentComplete = (a / 60) * 100
Next

End Sub

Ernst,

I've made some small changes in the code, it may or may not speed it up,
but it will be more efficient...
All the variables have been declared as I think appropriate. All of yours were variants,
except for "Late"
The array now is 60 by 3 instead of 61 by 4.
The progress bar has been replaced with a simple status bar display (much less overhead).
The check for blanks or text is replaced with a number verification.
The concatenation in the range addresses is replaced by the use of "Cells".

Give it a try and let us know. I have no way of testing the code without the data.

Regards,
Jim Cone
San Francisco, CA

REVISED CODE FOLLOWS...

Sub Sunday(week)
Dim TheArray() As Double
Dim a As Long
Dim R As Long
Dim c As Range
Dim e As Double
Dim Late As Boolean
Dim Lunch As Boolean
Dim After As Boolean
Dim Dinner As Boolean

a = 0
ReDim TheArray(1 to 60, 1 to 3)
Range("A9:C49").ClearContents

' Create the array.

For Each c In Range(week)
a = a + 1
TheArray(a, 1) = c.Value
TheArray(a, 2) = c.Cells(1, 2).Value
TheArray(a, 3) = c.Cells(1, -1).Value
Next 'c

' Sort the Array and display the values in order.

'BubbleSort TheArray

'Lag seems to start here...
R = 10

For a = 1 To UBound(TheArray)
e = TheArray(a, 1)
If Not IsNumeric(e) Then GoTo Bottom

If Lunch = False Then
If e = 0.458 Then
R = R + 1
Lunch = True
End If
End If

If After = False Then
If e = 0.58 Then
R = R + 7
After = True
End If
End If

If Dinner = False Then
If e = 0.708 Then
R = R + 1
Dinner = True
End If
End If

If Late = False Then
If e = 0.833 Then
R = R + 1
Late = True
End If
End If

Cells(R, 1).Value = TheArray(a, 1)
Cells(R, 2).Value = TheArray(a, 2)
Cells(R, 3).Value = TheArray(a, 3)

R = R + 1

Bottom:
Application.StatusBar = "PERCENT COMPLETE " & Format$((a / 60), "#00%")
Next 'a

End Sub


"Ernst Guckel" wrote in message ...
Hello,

I have a list of employees on a schedule. I have a piece of code look
them up, sort them by start time, and then put them on a daily line up for
printing.. Problem is that it takes a long time on the work PC's (200mhz).
The problem seems to be after the array returns sorted. I cannot seem to
figure it out. Any ideas?

Thanks...

-SNIP-

I've made some small changes in the code, it may or may not speed it up,
but it will be more efficient...
All the variables have been declared as I think appropriate. All of yours
were variants,
except for "Late"
The array now is 60 by 3 instead of 61 by 4.

The progress bar has been replaced with a simple status bar display (much
less overhead).
The check for blanks or text is replaced with a number verification.
The concatenation in the range addresses is replaced by the use of
"Cells".

Give it a try and let us know. I have no way of testing the code without
the data.

fixed a few of the issues that you see. After changing the code it ran
slower for some reason. I created a hybrid of the two. If providing the
data will help track down the lag I will be happy to send you the
spreadsheet...

BubbleSort sorts the employees by start time. So the listing has
organization.

Thanks for the help.
Ernst.

Ernst,

Well you never know until you test it. <g
There could be something else going on besides code problems.
With the amount of data you have, 60 x 3, the program should be complete
by the time you finish clicking the button.
Good luck with it.

Regards,
Jim Cone
San Francisco, CA

"Ernst Guckel" wrote in message ...
I've made some small changes in the code, it may or may not speed it up,
but it will be more efficient...
All the variables have been declared as I think appropriate. All of yours
were variants,
except for "Late"
The array now is 60 by 3 instead of 61 by 4.
The progress bar has been replaced with a simple status bar display (much
less overhead).
The check for blanks or text is replaced with a number verification.
The concatenation in the range addresses is replaced by the use of
"Cells".
Give it a try and let us know. I have no way of testing the code without
the data.

fixed a few of the issues that you see. After changing the code it ran
slower for some reason. I created a hybrid of the two. If providing the
data will help track down the lag I will be happy to send you the
spreadsheet...
BubbleSort sorts the employees by start time. So the listing has
organization.
Thanks for the help.
Ernst.

Well you never know until you test it. <g
There could be something else going on besides code problems.
With the amount of data you have, 60 x 3, the program should be complete
by the time you finish clicking the button.

it takes about 3 seconds at home but over 20 at work. Somethings not
right... :(

Ernst.