I'm trying to create a really long =if command in Excel VBA editor but I keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also! Please
Help!!!

Sub Usage()

Dim Usage As Double
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

K = Cell.K2
L = Cell.L2
M = Cell.M2
N = Cell.N2
O = Cell.O2
P = Cell.P2
Q = Cell.Q2
R = Cell.R2
S = Cell.S2
T = Cell.T2
U = Cell.U2
W = Cell.W2


Usage = (K + M + O + Q + S + U) / 6

If Usage 20 Then Cell.AA2 = (W / 15) Else:

If (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J / K)
(R / S) And (J / K) (T / U) Then Cell.AA2 = (J / K) Else:
If (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L / M)
(R / S) And (L / M) (T / U) Then Cell.AA2 = (L / M) Else:
If (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N / O)
(R / S) And (N / O) (T / U) Then Cell.AA2 = (N / O) Else:
If (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P / Q)
(R / S) And (P / Q) (T / U) Then Cell.AA2 = (J / K) Else:
If (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R / S)
(J / K) And (R / S) (T / U) Then Cell.AA2 = (R / S) Else:
If (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T / U)
(R / S) And (T / U) (J / K) Then Cell.AA2 = (T / U)
End If
End If
End If
End If
End If
End If
End If


Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select

End Sub

It looks like you're using the wrong syntax for cells.
If "K = Cell.K2" is supposed to assign the contents of K2 to the variable
K, you need to use the following syntax:

K = Range("K2").value
L = Range("L2").value
....

so also
If Usage 20 Then Cell.AA2 = (W / 15) Else:

becomes
If Usage 20 Then Range("AA2").value = (W / 15) Else:

You can use Cells(row,column) in place of Range(cell address)

Darren

On Fri, 16 Dec 2005 08:53:02 -0000, DA@PD""
wrote:

I'm trying to create a really long =if command in Excel VBA editor but I
keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also! Please
Help!!!

Sub Usage()

Dim Usage As Double
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

K = Cell.K2
L = Cell.L2
M = Cell.M2
N = Cell.N2
O = Cell.O2
P = Cell.P2
Q = Cell.Q2
R = Cell.R2
S = Cell.S2
T = Cell.T2
U = Cell.U2
W = Cell.W2


Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then Cell.AA2 = (W / 15) Else:

If (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J
/ K)
(R / S) And (J / K) (T / U) Then Cell.AA2 = (J / K) Else:
If (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L
/ M)
(R / S) And (L / M) (T / U) Then Cell.AA2 = (L / M) Else:
If (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N
/ O)
(R / S) And (N / O) (T / U) Then Cell.AA2 = (N / O) Else:
If (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P
/ Q)
(R / S) And (P / Q) (T / U) Then Cell.AA2 = (J / K) Else:
If (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R
/ S)
(J / K) And (R / S) (T / U) Then Cell.AA2 = (R / S) Else:
If (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T
/ U)
(R / S) And (T / U) (J / K) Then Cell.AA2 = (T / U)
End If
End If
End If
End If
End If
End If
End If


Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select

End Sub




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

Hi Darren,

Thanks for helping me classify the values!

"Darren Hill" wrote:

It looks like you're using the wrong syntax for cells.
If "K = Cell.K2" is supposed to assign the contents of K2 to the variable
K, you need to use the following syntax:

K = Range("K2").value
L = Range("L2").value
....

so also
If Usage 20 Then Cell.AA2 = (W / 15) Else:

becomes
If Usage 20 Then Range("AA2").value = (W / 15) Else:

You can use Cells(row,column) in place of Range(cell address)

Darren

On Fri, 16 Dec 2005 08:53:02 -0000, DA@PD""
wrote:

I'm trying to create a really long =if command in Excel VBA editor but I
keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also! Please
Help!!!

Sub Usage()

Dim Usage As Double
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

K = Cell.K2
L = Cell.L2
M = Cell.M2
N = Cell.N2
O = Cell.O2
P = Cell.P2
Q = Cell.Q2
R = Cell.R2
S = Cell.S2
T = Cell.T2
U = Cell.U2
W = Cell.W2


Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then Cell.AA2 = (W / 15) Else:

If (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J
/ K)
(R / S) And (J / K) (T / U) Then Cell.AA2 = (J / K) Else:
If (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L
/ M)
(R / S) And (L / M) (T / U) Then Cell.AA2 = (L / M) Else:
If (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N
/ O)
(R / S) And (N / O) (T / U) Then Cell.AA2 = (N / O) Else:
If (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P
/ Q)
(R / S) And (P / Q) (T / U) Then Cell.AA2 = (J / K) Else:
If (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R
/ S)
(J / K) And (R / S) (T / U) Then Cell.AA2 = (R / S) Else:
If (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T
/ U)
(R / S) And (T / U) (J / K) Then Cell.AA2 = (T / U)
End If
End If
End If
End If
End If
End If
End If


Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select

End Sub




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

This at least compiles and is somewhat more readable

Usage = (K + M + O + Q + S + U) / 6

If Usage 20 Then
Cell.AA2 = (W / 15)
ElseIf (J / K) (L / M) And (J / K) (N / O) And _
(J / K) (P / Q) And (J / K) (R / S) And _
(J / K) (T / U) Then
Cell.AA2 = (J / K)
ElseIf (L / M) (J / K) And (L / M) (N / O) And _
(L / M) (P / Q) And (L / M) (R / S) And _
(L / M) (T / U) Then Cell.AA2 = (L / M)
ElseIf (N / O) (L / M) And (N / O) (J / K) And _
(N / O) (P / Q) And (N / O) (R / S) And _
(N / O) (T / U) Then
Cell.AA2 = (N / O)
ElseIf (P / Q) (L / M) And (P / Q) (N / O) And _
(P / Q) (J / K) And (P / Q) (R / S) And _
(P / Q) (T / U) Then
Cell.AA2 = (J / K)
ElseIf (R / S) (L / M) And (R / S) (N / O) And _
(R / S) (P / Q) And (R / S) (J / K) And _
(R / S) (T / U) Then
Cell.AA2 = (R / S)
ElseIf (T / U) (L / M) And (T / U) (N / O) And _
(T / U) (P / Q) And (T / U) (R / S) And _
(T / U) (J / K) Then
Cell.AA2 = (T / U)
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"DA@PD" wrote in message
...
I'm trying to create a really long =if command in Excel VBA editor but I
keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also! Please
Help!!!

Sub Usage()

Dim Usage As Double
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

K = Cell.K2
L = Cell.L2
M = Cell.M2
N = Cell.N2
O = Cell.O2
P = Cell.P2
Q = Cell.Q2
R = Cell.R2
S = Cell.S2
T = Cell.T2
U = Cell.U2
W = Cell.W2


Usage = (K + M + O + Q + S + U) / 6

If Usage 20 Then Cell.AA2 = (W / 15) Else:

If (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J /
K)
(R / S) And (J / K) (T / U) Then Cell.AA2 = (J / K) Else:
If (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L /
M)
(R / S) And (L / M) (T / U) Then Cell.AA2 = (L / M) Else:
If (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N /
O)
(R / S) And (N / O) (T / U) Then Cell.AA2 = (N / O) Else:
If (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P /
Q)
(R / S) And (P / Q) (T / U) Then Cell.AA2 = (J / K) Else:
If (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R /
S)
(J / K) And (R / S) (T / U) Then Cell.AA2 = (R / S) Else:
If (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T /
U)
(R / S) And (T / U) (J / K) Then Cell.AA2 = (T / U)
End If
End If
End If
End If
End If
End If
End If


Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select

End Sub

Hi Bob,

I think you've got something that I can follow pretty well, but when I run
this in place of what I had before (starting at the usage line), I get an
object required error at "Cell.AA2=(W/15), the third line of the script.

Any ideas???

"Bob Phillips" wrote:

This at least compiles and is somewhat more readable

Usage = (K + M + O + Q + S + U) / 6

If Usage 20 Then
Cell.AA2 = (W / 15)
ElseIf (J / K) (L / M) And (J / K) (N / O) And _
(J / K) (P / Q) And (J / K) (R / S) And _
(J / K) (T / U) Then
Cell.AA2 = (J / K)
ElseIf (L / M) (J / K) And (L / M) (N / O) And _
(L / M) (P / Q) And (L / M) (R / S) And _
(L / M) (T / U) Then Cell.AA2 = (L / M)
ElseIf (N / O) (L / M) And (N / O) (J / K) And _
(N / O) (P / Q) And (N / O) (R / S) And _
(N / O) (T / U) Then
Cell.AA2 = (N / O)
ElseIf (P / Q) (L / M) And (P / Q) (N / O) And _
(P / Q) (J / K) And (P / Q) (R / S) And _
(P / Q) (T / U) Then
Cell.AA2 = (J / K)
ElseIf (R / S) (L / M) And (R / S) (N / O) And _
(R / S) (P / Q) And (R / S) (J / K) And _
(R / S) (T / U) Then
Cell.AA2 = (R / S)
ElseIf (T / U) (L / M) And (T / U) (N / O) And _
(T / U) (P / Q) And (T / U) (R / S) And _
(T / U) (J / K) Then
Cell.AA2 = (T / U)
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"DA@PD" wrote in message
...
I'm trying to create a really long =if command in Excel VBA editor but I
keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also! Please
Help!!!

Sub Usage()

Dim Usage As Double
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

K = Cell.K2
L = Cell.L2
M = Cell.M2
N = Cell.N2
O = Cell.O2
P = Cell.P2
Q = Cell.Q2
R = Cell.R2
S = Cell.S2
T = Cell.T2
U = Cell.U2
W = Cell.W2


Usage = (K + M + O + Q + S + U) / 6

If Usage 20 Then Cell.AA2 = (W / 15) Else:

If (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J /
K)
(R / S) And (J / K) (T / U) Then Cell.AA2 = (J / K) Else:
If (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L /
M)
(R / S) And (L / M) (T / U) Then Cell.AA2 = (L / M) Else:
If (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N /
O)
(R / S) And (N / O) (T / U) Then Cell.AA2 = (N / O) Else:
If (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P /
Q)
(R / S) And (P / Q) (T / U) Then Cell.AA2 = (J / K) Else:
If (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R /
S)
(J / K) And (R / S) (T / U) Then Cell.AA2 = (R / S) Else:
If (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T /
U)
(R / S) And (T / U) (J / K) Then Cell.AA2 = (T / U)
End If
End If
End If
End If
End If
End If
End If


Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select

End Sub

DA@PD:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements]...
[Else
[elsestatements]]
End If


Sub Usage()
Dim Usage As Double
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
K = [K2]
L = [L2]
M = [M2]
N = [N2]
O = [O2]
P = [P2]
Q = [Q2]
R = [R2]
S = [S2]
T = [T2]
U = [U2]
W = [W2]
Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then
[AA2] = (W / 15)
ElseIf (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J
/ K _
) (R / S) And (J / K) (T / U) Then
[AA2] = (J / K)
ElseIf (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L
/ M _
) (R / S) And (L / M) (T / U) Then
[AA2] = (L / M)
ElseIf (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N
/ O) _
(R / S) And (N / O) (T / U) Then
[AA2] = (N / O)
ElseIf (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P
/ Q) _
(R / S) And (P / Q) (T / U) Then
[AA2] = (J / K)
ElseIf (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R
/ S) _
(J / K) And (R / S) (T / U) Then
[AA2] = (R / S)
ElseIf (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T
/ U) _
(R / S) And (T / U) (J / K) Then
[AA2] = (T / U)
Else
MsgBox "No"
End If
Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"DA@PD" wrote:

I'm trying to create a really long =if command in Excel VBA editor but I keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also! Please
Help!!!

Sub Usage()

Dim Usage As Double
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

K = Cell.K2
L = Cell.L2
M = Cell.M2
N = Cell.N2
O = Cell.O2
P = Cell.P2
Q = Cell.Q2
R = Cell.R2
S = Cell.S2
T = Cell.T2
U = Cell.U2
W = Cell.W2


Usage = (K + M + O + Q + S + U) / 6

If Usage 20 Then Cell.AA2 = (W / 15) Else:

If (J / K) (L / M) And (J / K) (N / O) And (J / K) (P / Q) And (J / K)
(R / S) And (J / K) (T / U) Then Cell.AA2 = (J / K) Else:
If (L / M) (J / K) And (L / M) (N / O) And (L / M) (P / Q) And (L / M)
(R / S) And (L / M) (T / U) Then Cell.AA2 = (L / M) Else:
If (N / O) (L / M) And (N / O) (J / K) And (N / O) (P / Q) And (N / O)
(R / S) And (N / O) (T / U) Then Cell.AA2 = (N / O) Else:
If (P / Q) (L / M) And (P / Q) (N / O) And (P / Q) (J / K) And (P / Q)
(R / S) And (P / Q) (T / U) Then Cell.AA2 = (J / K) Else:
If (R / S) (L / M) And (R / S) (N / O) And (R / S) (P / Q) And (R / S)
(J / K) And (R / S) (T / U) Then Cell.AA2 = (R / S) Else:
If (T / U) (L / M) And (T / U) (N / O) And (T / U) (P / Q) And (T / U)
(R / S) And (T / U) (J / K) Then Cell.AA2 = (T / U)
End If
End If
End If
End If
End If
End If
End If


Selection.AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault
Range("AA2:AA3943").Select

End Sub

"DA@PD" wrote in message
...
I'm trying to create a really long =if command in Excel VBA editor but I
keep
getting multiple errors, the latest is a "End if with no block If" error.
But I know I'm setting up the character values incorrectly also!

If condition1 Then
action1
ElseIf condition2 Then
action2
ElseIf condition3 Then
action3
..
..
..
ElseIf conditionX Then
actionX
End If

Sub Usage()
Dim Usage As Double
Dim Counter1 As Byte
Dim Counter2 As Byte
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
J = Range("J2").Value
K = Range("K2").Value
L = Range("L2").Value
M = Range("M2").Value
N = Range("N2").Value
O = Range("O2").Value
P = Range("P2").Value
Q = Range("Q2").Value
R = Range("R2").Value
S = Range("S2").Value
T = Range("T2").Value
U = Range("U2").Value
W = Range("W2").Value
Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then
Range("AA2") = (W / 15)
Else: Counter2 = 10
For Counter1 = 1 To 6
Quotients(Counter1) = Cells(2, Counter2).Value / Cells(2, Counter2 + 1)
Counter2 = Counter2 + 2
Next Counter1
Range("AA2") = Application.Max(Quotients)
End If
Range("AA2").AutoFill Destination:=Range("AA2:AA3943"),
Type:=xlFillDefault
End Sub

I kept getting error with the filldown so I changed that to stop the
error but I'm not sure that you are intending for the value in AA2 to
be filled down to AA3943

I did it like this because it looked like you are wanting the maximum
out of J/K, L/M, N/O, P/Q, R/S and T/U to go into AA2 unless usage 20
(then AA2 = W/15)

Ken Johnson

(Hi Ken,

You certainly understand what I'm trying to do here, you are correct, I
don't want to have the contents in AA2 to just be posting in the subsequent
3,000 rows, I'd like the same type of calculation to be run on all of the
rows. Your programing is quite a bit above mine, so I couldn't really follow
the counters and quotiants, but I did try to run the script, but I kept
erroring with the fill down, so I just took it out, and now it gives me an
"object required" error on the "Range.("AA2")=W/15" line
any ideas why? Thanks for the help!

David

"Ken Johnson" wrote:

Sub Usage()
Dim Usage As Double
Dim Counter1 As Byte
Dim Counter2 As Byte
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
J = Range("J2").Value
K = Range("K2").Value
L = Range("L2").Value
M = Range("M2").Value
N = Range("N2").Value
O = Range("O2").Value
P = Range("P2").Value
Q = Range("Q2").Value
R = Range("R2").Value
S = Range("S2").Value
T = Range("T2").Value
U = Range("U2").Value
W = Range("W2").Value
Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then
Range("AA2") = (W / 15)
Else: Counter2 = 10
For Counter1 = 1 To 6
Quotients(Counter1) = Cells(2, Counter2).Value / Cells(2, Counter2 + 1)
Counter2 = Counter2 + 2
Next Counter1
Range("AA2") = Application.Max(Quotients)
End If
Range("AA2").AutoFill Destination:=Range("AA2:AA3943"),
Type:=xlFillDefault
End Sub

I kept getting error with the filldown so I changed that to stop the
error but I'm not sure that you are intending for the value in AA2 to
be filled down to AA3943

I did it like this because it looked like you are wanting the maximum
out of J/K, L/M, N/O, P/Q, R/S and T/U to go into AA2 unless usage 20
(then AA2 = W/15)

Ken Johnson

Regarding the filldown - if you copied the code from the email, Excel has
probably split the following into two lines:

Range("AA2").AutoFill Destination:=Range("AA2:AA3943"), Type:=xlFillDefault

It should be a single line - remove the line break and it'll be okay.

However, since it looks like you want to repeat this calculation across a
multiple rows, the filldown won't give you the result you are after.
Filldown will only copy what is in the cell - you won't have a formula in
the copied cell, you'll just have a result. You'll end up copying that
same result all the way down.

So, you need a different approach. Either a macro to install a (pretty
complex) formula into all the cells needed, or a loop in the code to
repeat the calculation for each of the required rows.

Can you explain, in fairly plain english, what it is exactly you need to
do and we'll be able to help better.


On Fri, 16 Dec 2005 18:01:01 -0000, DA@PD""
wrote:

(Hi Ken,

You certainly understand what I'm trying to do here, you are correct, I
don't want to have the contents in AA2 to just be posting in the
subsequent
3,000 rows, I'd like the same type of calculation to be run on all of the
rows. Your programing is quite a bit above mine, so I couldn't really
follow
the counters and quotiants, but I did try to run the script, but I kept
erroring with the fill down, so I just took it out, and now it gives me
an
"object required" error on the "Range.("AA2")=W/15" line
any ideas why? Thanks for the help!

David

Darren

Hi David,
Is that "." between "Range" and "(AA2")=W/15" in your code or just a
typo in your post?
When I add the "." it gives a Syntax Error and the code can't even be
started.
Ken Johnson

Does this do what you want?

(WARNING: if you have any zeros in k, M, O, Q, S, or U it'll fail - if
this does what yo uwant, we can add error handling code)

Sub Usage()

Dim Usage As Double
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 JK As Double
Dim LM As Double
Dim NO As Double
Dim PQ As Double
Dim RS As Double
Dim TU As Double
Dim fn As WorksheetFunction
Dim myResult As Double
Dim i As Long

Set fn = Application.WorksheetFunction
For i = 2 To 3943

J = Range("J" & i).Value
K = Range("K" & i).Value
L = Range("L" & i).Value
M = Range("M" & i).Value
N = Range("N" & i).Value
O = Range("O" & i).Value
P = Range("P" & i).Value
Q = Range("Q" & i).Value
R = Range("R" & i).Value
S = Range("S" & i).Value
T = Range("T" & i).Value
U = Range("U" & i).Value
W = Range("W" & i).Value

JK = J / K
LM = L / M
NO = N / O
PQ = P / Q
RS = R / S
TU = T / U


Usage = (K + M + O + Q + S + U) / 6


If Usage 20 Then
myResult = (W / 15)
ElseIf JK fn.Max(LM, NO, PQ, RS, TU) Then
myResult = JK
ElseIf LM fn.Max(JK, NO, PQ, RS, TU) Then
myResult = LM
ElseIf NO fn.Max(JK, LM, PQ, RS, TU) Then
myResult = NO
ElseIf PQ fn.Max(JK, LM, NO, RS, TU) Then
myResult = PQ
ElseIf RS fn.Max(JK, LM, NO, PQ, TU) Then
myResult = RS
ElseIf TU fn.Max(JK, LM, NO, PQ, RS) Then
myResult = TU
End If
Range("AA" & i).Value = myResult

Next i
Range("AA2:AA3943").Select

End Sub


On Fri, 16 Dec 2005 18:01:01 -0000, DA@PD""
wrote:

(Hi Ken,

You certainly understand what I'm trying to do here, you are correct, I
don't want to have the contents in AA2 to just be posting in the
subsequent
3,000 rows, I'd like the same type of calculation to be run on all of the
rows. Your programing is quite a bit above mine, so I couldn't really
follow
the counters and quotiants, but I did try to run the script, but I kept
erroring with the fill down, so I just took it out, and now it gives me
an
"object required" error on the "Range.("AA2")=W/15" line
any ideas why? Thanks for the help!

David

"Ken Johnson" wrote:

Sub Usage()
Dim Usage As Double
Dim Counter1 As Byte
Dim Counter2 As Byte
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
J = Range("J2").Value
K = Range("K2").Value
L = Range("L2").Value
M = Range("M2").Value
N = Range("N2").Value
O = Range("O2").Value
P = Range("P2").Value
Q = Range("Q2").Value
R = Range("R2").Value
S = Range("S2").Value
T = Range("T2").Value
U = Range("U2").Value
W = Range("W2").Value
Usage = (K + M + O + Q + S + U) / 6
If Usage 20 Then
Range("AA2") = (W / 15)
Else: Counter2 = 10
For Counter1 = 1 To 6
Quotients(Counter1) = Cells(2, Counter2).Value / Cells(2, Counter2 + 1)
Counter2 = Counter2 + 2
Next Counter1
Range("AA2") = Application.Max(Quotients)
End If
Range("AA2").AutoFill Destination:=Range("AA2:AA3943"),
Type:=xlFillDefault
End Sub

I kept getting error with the filldown so I changed that to stop the
error but I'm not sure that you are intending for the value in AA2 to
be filled down to AA3943

I did it like this because it looked like you are wanting the maximum
out of J/K, L/M, N/O, P/Q, R/S and T/U to go into AA2 unless usage 20
(then AA2 = W/15)

Ken Johnson





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

Hi David,
Or this? Which probably does the same as Darren's and can result in
Division by zero error if Quotient calculation involves division by a
cell with value = zero.

Sub Usage()
Dim Usage As Double
Dim Counter1 As Byte
Dim Counter2 As Byte
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
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, 22).Value
Usage = (K + M + O + Q + S + U) / 50
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
End Sub

Ken Johnson