+ Reply to Thread
Results 1 to 4 of 4

Search if a cell range contains a particular value

  1. #1
    PCLIVE
    Guest

    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. #2
    Jim Thomlinson
    Guest

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


  3. #3
    Steve
    Guest

    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. #4
    PCLIVE
    Guest

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

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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