Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Advanced FIND technique

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Austin, TX
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd like to edit an existing macro so that it looks in a range of selected cells for the word NO. If it doesn't find any cells with the word NO within the selected range, then I want it to delete the worksheet. If it does find a NO, then I want it to stop so the user can look at the data. Any ideas?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here it is:

    Code:
    Sub alrightyThen()
    Dim n As Range
    Set n = Selection.Find(what:="NO", LookAt:=xlWhole)
    If n Is Nothing Then
    Application.DisplayAlerts = False
    On Error GoTo 1:
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    End If
    End
    1: MsgBox ("You must have at least one visible worksheet in a standard .xls file")
    End Sub
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-10 15:22 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 14:51, agiammo wrote:
    I'd like to edit an existing macro so that it looks in a range of selected cells for the word NO. If it doesn't find any cells with the word NO within the selected range, then I want it to delete the worksheet. If it does find a NO, then I want it to stop so the user can look at the data. Any ideas?
    this isn't pretty, but I think it works:

    Sub test()
    ws = 1
    wsCount = ActiveWorkbook.Sheets.Count - 1
    While ws <= (wsCount)
    Sheets(ws).Select
    With Worksheets(ws).Range("a1:a500") 'Adjust for your range
    Set c = .Find("NO", LookIn:=xlValues)
    If c Is Nothing Then
    Application.DisplayAlerts = False
    Worksheets(ws).Delete
    Application.DisplayAlerts = True
    'ws = ws - 1
    wsCount = wsCount - 1
    Else: ws = ws + 1
    End If
    End With
    Wend
    End Sub

    You will have to adjust for your range. I also have it look for one less sheet than you have in your workbook because it will crash if it tries to delete every sheet. I'm sure this could be written better and more efficiently.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See, not only am I slow, but NateO's is much nicer code.

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bergy, you were right on target, your code did a little more than mine, looping through each sheet. Have a great weekend all.

  6. #6
    Board Regular
    Join Date
    May 2002
    Location
    Austin, TX
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    I tried using Nate's code (I didn't need to look on each tab, just the active tab) but it doesn't seem to work as an IF, THEN statement. Even when I plug in a 'NO' the macro still deletes the sheet as if nothing were wrong. And it always gives me the message box too. Any ideas on how to firm this up?

  7. #7
    Board Regular
    Join Date
    May 2002
    Location
    Austin, TX
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I know where the problem is, but my 'fixes' aren't working. here is the code:

    'Dim n As Range
    Set n = Selection.Find(what:="NO", LookAt:=xlWhole)
    If n Is Nothing Then
    Application.DisplayAlerts = False
    On Error GoTo 1:
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    End If

    Now, the selected range of cells has a formula in it that returns either YES or NO. Do I need to specify that it search for a value? I tried changing this line:

    If n Is Nothing Then
    to this:
    If n Is "YES" Then

    and it didn't work...got some compile error.

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can't have the apostrophe in front of your Dim statement. I got the following to work on both xl2000 and xl97. I added an upper lower case so that the macro is not too finicky, if this doesn't help, please post back.

    Code:
    Sub alrightyThen()
    Dim n As Range
    Set n = Selection.Find(what:="NO", LookAt:=xlWhole, MatchCase:=False)
    If n Is Nothing Then
    Application.DisplayAlerts = False
    On Error GoTo 1:
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    End If
    End
    1: MsgBox ("You must have at least one visible " _
     & "worksheet in a standard .xls file")
    End Sub
    This is testing the area you have selected, vs. a predefined range, is this what you want?

    But it does indeed work as an if statement:

    It looks at each range in the selection and thinks if there are zero cells in this selection, than this worksheet is history, otherwise, it stays.

    Hope this helps.
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-14 08:57 ]

  9. #9
    Board Regular
    Join Date
    May 2002
    Location
    Austin, TX
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Nate,

    I use this function several times in the macro, so I had 'noted' the definition line...sorry for the confusion.

    I do want to test only the selected range. It changes from week to week, so I cannot pre-define it.

    Let me test this and I'll post back!

  10. #10
    Board Regular
    Join Date
    May 2002
    Location
    Austin, TX
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Nate,

    Ok, this is still not working. you said the code is looking in the selection, and if there are zero cells in the selection, then it deletes? That doesn't make sense to me. I have several hundred rows that are selected. Each cell has a formula that compares one cell against another. If they match, then the formula returns a YES value. If the cells do not match, then I see NO. If all the cells are YES, then I can delete the active sheet and move on. If I get a NO, then I need to stop or pause the macro so that I can look at the data and make corrections. Currently, the macro is deleting the active sheet in both cases.

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
  •