Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding maximum, minimum in a range consists both Positive and Negative numbers | Excel Worksheet Functions | |||
formula for finding out the negative numbers | Excel Worksheet Functions | |||
Finding a value quickly using VBA | Excel Discussion (Misc queries) | |||
math; How do I multiply a row of numbers quickly? | Excel Discussion (Misc queries) | |||
What is filtered in a Pivot Table? Finding out quickly... | Excel Discussion (Misc queries) |