Validate a result
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 |
Validate a result
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 |
Validate a result
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 |
All times are GMT +1. The time now is 04:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com