# Check for 0's

#### rmwilson

##### Board Regular
Need a macro that will search the spreadsheet from column B to IV for 0. If not found then a message box saying 0 not found at column A cell content (i.e) 125. There will only be 1 zero in every other row starting in row 1.

..A........B..........C.........D............E.........F
.......-25.99...-58.58.......0.........36.52....42.25
100...425.22...350.05...500.00...700.65...658.98
.......-20.99...-38.58.....36.04....48.25
125...425.22...350.05...500.00...700.65
.......-19.99...-18.38.......0.........36.52....42.25
100...425.22...350.05...500.00...700.65...658.98
.......-20.99...-38.58.......0.........48.25....19.25
150...425.22...350.05...500.00...700.65..600.05

Thanks!

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try this (this will return value in column A of the first instance of not having a zero [if there is one]):

Code:
``````Sub checkzeroes()
Dim i As Long
Dim c As Range
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 2
Set c = Range("A" & i & ":IV" & i + 1).Find(0, LookAt:=xlWhole)
If c Is Nothing Then
Exit Sub
End If
Next i
End Sub``````

Thanks Oaktree for your reply. Can this be modified for multiple instances?.

Thanks!

rmw

By multiple instances, do you mean return the values from column A where 2 or more 0's are present in the 2 corresponding lines?

Try this:

Code:
``````Sub checkzeroes()
Dim i As Long
Dim my_count As Integer
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 2
my_count = WorksheetFunction.CountIf(Range("A" & i & ":IV" & i + 1), 0)
If my_count > 1 Then
MsgBox "more than one 0 found at " & Cells(i + 1, 1)
Exit Sub
End If
Next i
End Sub``````

but, note that COUNTIF is much slower than the .find method from the first approach...

Yes Sir.

Oaktree,

Sorry about that. zeros not present in every other line.

..A........B..........C.........D............E.........F
.......-25.99...-58.58.......0.........36.52....42.25
100...425.22...350.05...500.00...700.65...658.98
.......-20.99...-38.58.....36.04....48.25
125...425.22...350.05...500.00...700.65
.......-19.99...-18.38.....36.52....42.25
150...425.22...350.05...500.00...700.65...658.98

If you want a message box for each occurrence, just delete the words "Exit Sub" from the code.

Thanks!

Replies
3
Views
1K

1,203,174
Messages
6,053,908
Members
444,694
Latest member
JacquiDaly

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back