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

Search if a cell range contains a particular value
 

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

Search if a cell range contains a particular value
 

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

Search if a cell range contains a particular value
 

Thank you Steve.

That did exactly what I wanted.

Thanks again.
Paul


"Steve" wrote in message
...
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"
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