Can someone correct my macro please cos I'm soooooo close?
Been working on this sheet for days now and starting to get punch 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 have 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:i27 instead of 5000.
How do I adapt this macro to do this as all I keep getting is syntax errors
Thanks to all
Cheers
Nobby

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


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556609

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

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


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556609