Hi Ken,
I can see how that 22/23 error crept in - the amount of times I've mucked
up referring to columns by numbers. Grrr.
I like the way you solved the Divide by zero problem.
David, if you do use my code or some variant of it, it's a good idea to
stick Ken's line:
Application.ScreeenUpdating = False
at the start. It's handy in any macro which modifies a lot of worksheet
cells - it can make a massive speed difference.

I usually put
Application.ScreeenUpdating = True
at the end of the code, just before End Sub, to reset it. Apparently it's
not actually necessary, Excel should reset itself automatically. It's that
"should" that worries me :). Maybe earlier versions than xl2002 needed it?

Darren
On Sat, 17 Dec 2005 01:19:08 -0000, Ken Johnson
wrote:

Hi Darren and David,

Wasn't such a big problem after all!
Where I should have been referencing column W (Column 23), I was
accidentally referencing column V (column 22), and luckily this
reference is only in the main loop.
If it occurred in the other loops my code would have been further
complicated by the addition of If statements. So I only had to change a
"22" to a "23" and that solved that problem.

I have also:
1. Fixed up the "6" that I had changed to a "50"
2. Added "On Error Resume Next" to deal with division by zero
errors
3. Added Application.ScreenUpdating = False to significantly speed up
the code.

However, I would still use your code Darren! It's easier to fix up
future problem simply because it's easier to follow.

Sub Usage()
Application.ScreenUpdating = False
Dim Usage As Double
Dim Counter1 As Byte
Dim Counter2 As Long
Dim Counter3 As Long
Dim J As Double
Dim K As Double
Dim L As Double
Dim M As Double
Dim N As Double
Dim O As Double
Dim P As Double
Dim Q As Double
Dim R As Double
Dim S As Double
Dim T As Double
Dim U As Double
Dim W As Double
Dim Quotients(1 To 6) As Double
Dim QuotientMax As Double
On Error Resume Next
For Counter3 = 2 To 3943
J = Cells(Counter3, 10).Value
K = Cells(Counter3, 11).Value
L = Cells(Counter3, 12).Value
M = Cells(Counter3, 13).Value
N = Cells(Counter3, 14).Value
O = Cells(Counter3, 15).Value
P = Cells(Counter3, 16).Value
Q = Cells(Counter3, 17).Value
R = Cells(Counter3, 18).Value
S = Cells(Counter3, 19).Value
T = Cells(Counter3, 20).Value
U = Cells(Counter3, 21).Value
W = Cells(Counter3, 23).Value
Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then
Cells(Counter3, 27) = (W / 15)
Else: Counter2 = 10
For Counter1 = 1 To 6
Quotients(Counter1) = Cells(Counter3, Counter2).Value / Cells(Counter3,
Counter2 + 1)
Counter2 = Counter2 + 2
Next Counter1
Cells(Counter3, 27) = Application.Max(Quotients)
For Counter1 = 1 To 6
Quotients(Counter1) = 0
Next Counter1
End If
Next Counter3
On Error GoTo 0
End Sub

Ken Johnson

PS I'm getting a lot of Google Groups Server Errors today. It's so
annoying having to do things more than once. I've resorted to typing
my posts in Word then pasting into Groups.




--
------------------
Darren