find a value

[Guest]

I need a code that will search Sheet1.column A from A1:A58 and A72:A89 and
return in cell B1 the text "true" if the number "999" is found in the target
range one or more times.

 

[KL]

Hi Shawn,

Try this:

Sub test()
Dim x As Boolean
With Application
x = (.CountIf(Sheets("Sheet1").Range("A1:A58"), 999) _
+ .CountIf(Sheets("Sheet1").Range("A72:A89"), 999)) > 1
End With
Sheets("Sheet1").Range("B1").Value = x
End Sub

Regards,
KL

 

[Guest]

I used the variation below and it worked. Your way did not. Do you see a
problem with what I have below (it seems to work)

Sub test()
Dim x As Boolean
With Application
x = .CountIf(Sheets("Sheet1").Range("A1:A58"), 999) _
+ .CountIf(Sheets("Sheet1").Range("A72:A89"), 999)
End With
Sheets("Sheet1").Range("B1").Value = x
End Sub

--
Thanks
Shawn

Hi Shawn,

Try this:

Sub test()
Dim x As Boolean
With Application
x = (.CountIf(Sheets("Sheet1").Range("A1:A58"), 999) _
+ .CountIf(Sheets("Sheet1").Range("A72:A89"), 999)) > 1
End With
Sheets("Sheet1").Range("B1").Value = x
End Sub

Regards,
KL

 

[Tom Ogilvy]

That should work.

--
Regards,
Tom Ogilvy

I used the variation below and it worked. Your way did not. Do you see a
problem with what I have below (it seems to work)

Sub test()
Dim x As Boolean
With Application
x = .CountIf(Sheets("Sheet1").Range("A1:A58"), 999) _
+ .CountIf(Sheets("Sheet1").Range("A72:A89"), 999)
End With
Sheets("Sheet1").Range("B1").Value = x
End Sub

 

[KL]

Hi Shawn,

You are right I mistakenly assumed you wanted TRUE if 999 appeared more than
once.

Regards,
KL

I used the variation below and it worked. Your way did not. Do you see a
problem with what I have below (it seems to work)

Sub test()
Dim x As Boolean
With Application
x = .CountIf(Sheets("Sheet1").Range("A1:A58"), 999) _
+ .CountIf(Sheets("Sheet1").Range("A72:A89"), 999)
End With
Sheets("Sheet1").Range("B1").Value = x
End Sub