Finding negative numbers quickly
 

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

Finding negative numbers quickly
 

Try creating a macro that validates the cell value based on sheet update.

That is the macro fires each time a cell change occurs.

"Ian Parker" wrote in message
...
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

Finding negative numbers quickly
 

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

--
Regards,
Tom Ogilvy

Ian Parker wrote in message
...
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

Finding negative numbers quickly - Thanks for help.
 

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

"Ian Parker" wrote in message
...
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