Finding negative numbers quickly

  • Thread starter Thread starter Ian Parker
  • Start date Start date
I

Ian Parker

Good day all,
I have looked at various "find" posts on the news group but cannot find an
example of what I am trying to achieve.
Basic history (simplified) is a purchasing, sales and inventory spreadsheet
(PSI).

When a user closes the spreadsheet they are in, I would like my vba app to
check for any negative numbers in the entire sheet called "PSI" and abort
the save/close if one is found. As some of the fields are entered values and
some are calculations it is a bit tricky. Also as some of the formulas have
minus signs in them you cannot just search for -

At this stage I use conditional formating to highlight negatives and I can't
use data valadation due to the calculations.
I know that I could check every cell to see if its value is less than zero
but it seems there must be a smarter way.

In summary
On file close check to see if any numbers ( entered or calculated) are less
than zero

Thanks in advance
Ian Parker
 
Try creating a macro that validates the cell value based on sheet update.

That is the macro fires each time a cell change occurs.
 
If you look at xlValues, you can use find to seek the negative sign and it
won't look at the formulas containing a minus sign.
Dim rng as range, rng1 as range
Dim rng2 as range, rng3 as range
set rng = cells.specialcells(xlConstants, xlNumbers)
set rng1 = cells.specialcells(xlFormulas, xlNumbers)
if not rng is nothing and not rng1 is nothing then
set rng2 = union(rng,rng1)
set rng3 = rng2.find("-", Lookin:=xlValues, _
Lookat:=xlPart)

and so forth
 
Thanks all for your help.
Pieced together the below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Set rng = Cells.SpecialCells(xlConstants, xlNumbers)
Set rng1 = Cells.SpecialCells(xlFormulas, xlNumbers)
If Not rng Is Nothing And Not rng1 Is Nothing Then
Set rng2 = Union(rng, rng1)
Set rng3 = rng2.Find("-", LookIn:=xlValues, _
Lookat:=xlPart)
If Not rng3 Is Nothing Then
MsgBox "Negative Found " & rng3.Address
rng3.Select
Cancel = True
End If
End If
End Sub
 
Back
Top