Search a range for a value

[Guest]

Below is my code. It is supposed to look for the value "999" in the two
ranges specified and if "999" is in either range give the message box.
However, it doesn't seem to be working. Here are a couple possible problems.
Other values are in some of the cells in the target range (i.e. "898").
Everyother cell in the target range is merged together (i.e. AD18 and AD19
are merged into one cell). Please advise.

Dim x As Boolean
With Application
x = .CountIf(Sheets("T.S. (2)").Range("AD18:AD44"), 999) +
..CountIf(Sheets("T.S. (2)").Range("AD67:AD99"), 999)
End With

If x = True Then

Msg = "This employee has included the 999 code on his/her time
sheet! Is this a 75 hour leave without pay time sheet?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
With Worksheets("Worksheet")
.Range("B9:I15,L9:S15,V4").Value = 0
End With
End If
If Ans = vbNo Then
Msg = "Please remove the 999 code from their time sheet."
Ans = MsgBox(Msg, vbOKOnly)
ActiveWindow.ActivateNext
End If
End If

 

[Guest]

Try the following.

Dim x1 As Boolean
Dim x2 As Boolean
With Application
x1 = .CountIf(Sheets("T.S. (2)").Range("AD18:AD44"), 999)
x2 = .CountIf(Sheets("T.S. (2)").Range("AD67:AD99"), 999)
End With

If (x1 Or x2) = True Then

Msg = "This employee has included the 999 code on his/her time
sheet! Is this a 75 hour leave without pay time sheet?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
With Worksheets("Worksheet")
.Range("B9:I15,L9:S15,V4").Value = 0
End With
End If
If Ans = vbNo Then
Msg = "Please remove the 999 code from their time sheet."
Ans = MsgBox(Msg, vbOKOnly)
ActiveWindow.ActivateNext
End If
End If


I did some minimal testing. Let me know if it doesn't work like it should.