Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: returning cell number

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a list with either a "GO" or "STOP" in a particular cell. This is an extraordinarily long list, so I need to know which cells have "GO" in them. How can I get a list of those cell numbers?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    alrighty, in another cell somewhere use the following (i assume your list is in column B, as an example)...

    =COUNTIF("B:B","GO")

    play with it, you can put any range and any value to search for.

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I assume you know the Column your list is in. Let's Say Col A beginning with A2. In B2 (Insert a temporary col if needed) enter
    =IF(A2="GO"),"A"&ROW(),""). Copy down the page. This will place the Cell address next to each cell in Col A that contains "GO". You can then copy this coluumn to a new location, remove the blanks and produce your list.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help. That solves of returning th cell address. Unfortunately, the lists are incredibly long (like 12000 rows), so it would be difficult to copy, paste, and take out the blanks of the lists. Is there an easier way to get excel to put the addresses in a range of cells (I know that only 9 out of 12000 cells have "GO")?


    On 2002-04-04 14:33, lenze wrote:
    I assume you know the Column your list is in. Let's Say Col A beginning with A2. In B2 (Insert a temporary col if needed) enter
    =IF(A2="GO"),"A"&ROW(),""). Copy down the page. This will place the Cell address next to each cell in Col A that contains "GO". You can then copy this coluumn to a new location, remove the blanks and produce your list.

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help. That solves of returning th cell address. Unfortunately, the lists are incredibly long (like 12000 rows), so it would be difficult to copy, paste, and take out the blanks of the lists. Is there an easier way to get excel to put the addresses in a range of cells (I know that only 9 out of 12000 cells have "GO")?


    On 2002-04-04 14:33, lenze wrote:
    I assume you know the Column your list is in. Let's Say Col A beginning with A2. In B2 (Insert a temporary col if needed) enter
    =IF(A2="GO"),"A"&ROW(),""). Copy down the page. This will place the Cell address next to each cell in Col A that contains "GO". You can then copy this coluumn to a new location, remove the blanks and produce your list.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    could you use autofilter (data menu)?

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks! That works.

    On 2002-04-04 15:15, anno wrote:
    could you use autofilter (data menu)?

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could use VBA, pretty simple:

    Sub FindGo()
    Dim counter As Integer
    Dim rng As Range
    Set rng = Intersect(ActiveSheet.UsedRange, Columns("a"))
    For Each c In rng
    If UCase(c.Value) = "GO" Then
    counter = counter + 1
    Range("b" & counter).Value = c.Address
    End If
    Next c
    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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