Coding

[Ed]

What would be the code in a macro to accomplish the following:


If the (maximum of cell range A5 thru A9) is greater than the
(average of cells A5 thru A9) times 1.05 or if the (minimum of cell
range A5 thru A9) is less than the (average of cell A5 thru A9) times
0.95 then A10 equals "Fail" else A10 = "Pass"


Some time back I got information on a similar problem, but I had a
computer crash and I lost it.

Any help would be appreciated.


Ed English

 

[Mike H]

Hi,

If I've iunderstood correctly, try this

Dim MyRange As Range
Set MyRange = Range("A5:A9")
If WorksheetFunction.Max(MyRange) > WorksheetFunction.Average(MyRange) *
1.05 Or _
WorksheetFunction.Min(MyRange) < WorksheetFunction.Average(MyRange) * 0.05
Then
Range("A10").Value = "Fail"
Else
Range("A10").Value = "Pass"
End If

Mike

 

[Mike H]

Typo,

Try this instead

Dim MyRange As Range
Set MyRange = Range("A5:A9")
If WorksheetFunction.Max(MyRange) > WorksheetFunction.Average(MyRange) *
1.05 Or _
WorksheetFunction.Min(MyRange) < WorksheetFunction.Average(MyRange) * 0.95
Then
Range("A10").Value = "Fail"
Else
Range("A10").Value = "Pass"
End If

Mike

 

[Ed]

Mike,

Tried both and got compile error. will review and advise.

Thanks for replies.


Ed English

 

[Ed]

It is a syntax error

Typo,

Try this instead

Dim MyRange As Range
Set MyRange = Range("A5:A9")
If WorksheetFunction.Max(MyRange) > WorksheetFunction.Average(MyRange) *
1.05 Or _
WorksheetFunction.Min(MyRange) < WorksheetFunction.Average(MyRange) * 0.95
Then
Range("A10").Value = "Fail"
Else
Range("A10").Value = "Pass"
End If

Mike

 

[Mike H]

Ed,

The syntax error is caused by the post line-wrapping.

This is all one line

If WorksheetFunction.Max(MyRange) > WorksheetFunction.Average(MyRange) *
1.05 Or _

Mike

 

[Ed]

Mike,

I did get it to run. Thanks for your help.


Ed English