Identifing values in a currency column

[sup191]

I'm trying to figure out how to scan a column of currency values an
identify if any of the cells contain a blank cell, negative value, or
zero and pop up a message letting me know. I paste anywhere up t
50,000 numbers into a column and any of the three instances mentione
above will cause the program I import the data into to crash out.
know I could always do an autosort and check the values, but I wa
hopeing to automate the inputting process as much as possible. If
could get a popup letting me know, I could instantly reject th
spreadsheet back for errors. I usually do anywhere betwen 500-1000 o
these spreadsheet entries a day. (Yeah, I know - what a boring ju
huh?)

Thanks ahead of time for any help on this problem

 

[Bob Phillips]

This colours every offending cell in red to highlight them

For i = 1 To Cells(Rows.VCount).End(xlUp).Row
If Cells(i,"A").Value ="" Or Cells(i,"A").Value = 0 Or _
Cells(i,"A").Value < 0 Then
Cells(i,"A").Interior.ColorIndex = 3
Else
Cells(i,"A").Interior.ColorIndex = xlColorindexNone
End If
Next i

Create a macro and attach it to a button.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Possibly something like this:

Sub AAAtester1()
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Debug.Print Application.CountIf(rng, "<=0"), Application.CountBlank(rng)
If Application.CountIf(rng, "<=0") > 0 Or _
Application.CountBlank(rng) > 0 Then
MsgBox "Reject Data"
End If

End Sub

 

[sup191]

Thanks to both of you, that did it!

I have to grab myself a Visual Basic book one of these days... :

 

[Bob Phillips]

You have a dynamic, interactive one here <g>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)