Search if a cell range contains a particular value

I'm trying to write a macro that will look at a selected range of cells and
if any of them contains a certain value, then a number 1 will be put in a
paticular cell, say...B9.
Here is what I have.


Range("D" & (a) & ":D" & (b)).Select
If Selection = "Test" Then Range("S" & a).Value = 1



It obviously doesn't work, but I'm looking for suggestions

Thanks,
Paul

Public Sub FindValue()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Range("B:B")
Set rngCurrent = rngToSearch.Find("Tada")

If rngCurrent Is Nothing Then
MsgBox "Tada was not found."
Else
rngCurrent.Offset(0, 1).Value = "Found"
End If
End Sub
--
HTH...

Jim Thomlinson


"PCLIVE" wrote:

> I'm trying to write a macro that will look at a selected range of cells and
> if any of them contains a certain value, then a number 1 will be put in a
> paticular cell, say...B9.
> Here is what I have.
>
>
> Range("D" & (a) & ":D" & (b)).Select
> If Selection = "Test" Then Range("S" & a).Value = 1
>
>
>
> It obviously doesn't work, but I'm looking for suggestions
>
> Thanks,
> Paul
>
>
>

paul, you might try a for each statement to test the cells
individually....below will test range D1:D10

a = 1
b = 10

Range("D" & a & ":D" & b).Select

For Each cell In Selection
If cell.Value = "test" Then
Range("S" & a).Value = 1
End If
Next cell


hth
steve



On Wed, 31 Aug 2005 17:21:25 -0400, "PCLIVE" <[email protected]>
wrote:

>I'm trying to write a macro that will look at a selected range of cells and
>if any of them contains a certain value, then a number 1 will be put in a
>paticular cell, say...B9.
>Here is what I have.
>
>
>Range("D" & (a) & ":D" & (b)).Select
>If Selection = "Test" Then Range("S" & a).Value = 1
>
>
>
> It obviously doesn't work, but I'm looking for suggestions
>
>Thanks,
>Paul
>
>

Thank you Steve.

That did exactly what I wanted.

Thanks again.
Paul


"Steve" <[email protected]> wrote in message
news:[email protected]...
> paul, you might try a for each statement to test the cells
> individually....below will test range D1:D10
>
> a = 1
> b = 10
>
> Range("D" & a & ":D" & b).Select
>
> For Each cell In Selection
> If cell.Value = "test" Then
> Range("S" & a).Value = 1
> End If
> Next cell
>
>
> hth
> steve
>
>
>
> On Wed, 31 Aug 2005 17:21:25 -0400, "PCLIVE" <[email protected]>
> wrote:
>
>>I'm trying to write a macro that will look at a selected range of cells
>>and
>>if any of them contains a certain value, then a number 1 will be put in a
>>paticular cell, say...B9.
>>Here is what I have.
>>
>>
>>Range("D" & (a) & ":D" & (b)).Select
>>If Selection = "Test" Then Range("S" & a).Value = 1
>>
>>
>>
>> It obviously doesn't work, but I'm looking for suggestions
>>
>>Thanks,
>>Paul
>>
>>
>