Check for 0's

[rmwilson]

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!

 

[Oaktree]

Try this (this will return value in column A of the first instance of not having a zero [if there is one]):

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
        MsgBox "0 not found at " & Cells(i + 1, 1)
        Exit Sub
    End If
Next i
End Sub

 

[rmwilson]

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

Thanks!

rmw

 

[Oaktree]

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:

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...

 

[rmwilson]

Yes Sir.

 

[rmwilson]

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

message box..zero not found in 125
message box..zero not found in 150

 

[Oaktree]

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

 

[rmwilson]

Thanks!