# Search if a cell range contains a particular value

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

2. ## RE: 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")

If rngCurrent Is Nothing Then
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
>
>
>

3. ## Re: 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" <pclive@livenet.net>
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
>
>

4. ## Re: Search if a cell range contains a particular value

Thank you Steve.

That did exactly what I wanted.

Thanks again.
Paul

"Steve" <stevepegg@telstra.com> wrote in message
news:43167611.75645031@news.cdn.telstra.com.au...
> 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" <pclive@livenet.net>
> 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
>>
>>

>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1