Validate a result

N

nobbyknownowt

Can someone correct my macro please cos I'm soooooo close?
Been working on this sheet for days now and starting to get punc
drunk!

I have borrowed this off the site but cannot adapt it to my needs.

I wish to validate a result in a cell and display a msgbox once I hav
gone over a target figure.
The macro
Private Sub Worksheet_Calculate()
If Me.Range("o16").Value > 5000 Then
MsgBox "too high - do something!"
End If
End Sub
Works great once I get to over 5000
But i want it to pick its own maximum total from the sum of d27:i2
instead of 5000.
How do I adapt this macro to do this as all I keep getting is synta
errors
Thanks to all
Cheers
Nobb
 
J

jetted

HI

A quick way would be
Private Sub Worksheet_Calculate()
d27 = Range("d27").Value
e27 = Range("e27").Value
f27 = Range("f27").Value
g27 = Range("g27").Value
h27 = Range("h27").Value
i27 = Range("i27").Value
max_val = d27 + e27 + f27 + g27 + h27 + i27
If Me.Range("o16").Value > max_val Then
MsgBox "too high - do something!"
End If
End Sub
 
N

nobbyknownowt

I love quick ways.
Thanks
This works great until I clear my data sheet, then however the d27 et
totals become #N/A due to data missing in the previous calculations an
this activates the msgbox warning. Is there a way I can either only ru
the code when there is data in a certain cell or change #N/A to
IF(d27=#N/A,1,0) dont work.
Thanks
Nobb
 
J

jetted

Hi nobby

Sorry for the late response I was on vacation. I would try this for
your problem

Private Sub Worksheet_Calculate()
max_val=0
For Each n In Range("D27:I27")
If IsNumeric(n) Then
max_val = n + max_val
End If
Next n
If Range("o16").Value > max_val Then
MsgBox "too high - do something!"
End If
End Sub
 
Top