Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Advanced FIND technique

  1. #11
    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 said the code is looking in the selection, and if there are zero cells in the selection, then it deletes?
    No that's not what it does, it searches every cell [in your selection] for the word NO, if it finds it, it quits. If it can't find it, it deletes the sheet (unless it's the only visible sheet, not allowed in a normal .xls file).

    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.
    This isn't the issue. How you're selecting your cells may be the issue (you don't really need to select them). How do you perform this operation?

    The code above still works for me, try it manually (highlight a range with No, does it delete the sheet? Highlight a range without No, does it delete the sheet?)

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-14 09:04 ]

  2. #12
    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

    But, since you're using a formula, let's add the value statement:

    Code:
    Sub alrightyThen()
    Dim n As Range
    Set n = Selection.Find(what:="NO", LookAt:=xlWhole, _
        LookIn:=xlValues, 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
    The find is the same as using ctrl-f, this just streamlines the search range.

    Hope this helps.

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

    Default

    Maybe this will help.
    I really appreciate all your input!

    Sheets("Data").Select
    Sheets("Data").Copy After:=Sheets(41)
    Sheets("Data").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False

    Sheets("Data (2)").Select
    Range("K2").Select

    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=Data!RC[-5],""yes"",""NO"")"
    Range("K2").Select
    Selection.Copy
    Range("K2:N2").Select
    ActiveSheet.Paste

    Dim i As Long
    i = [F65536].End(xlUp).Row
    [k2] = "=IF(F2=Data!F2,""yes"",""NO"")"
    [k2].Copy Range("k2:n" & i)
    Range("K2:N" & i).Select

    Dim n As Range
    On Error Resume Next
    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

    1: MsgBox ("There is a problem with the data.")

  4. #14
    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

    Try replacing your last three sections with:


    Dim i As Long
    Dim z As Range
    i = [F65536].End(xlUp).Row
    [k2] = "=IF(F2=Data!F2,""yes"",""NO"")"
    [k2].Copy Range("k2:n" & i)
    Set z = Sheets(42).Range("K2:N" & i)

    Dim n As Range
    Set n = z.Find(what:="NO", LookAt:=xlWhole, _
    LookIn:=xlValues, MatchCase:=False)
    If n Is Nothing Then
    Application.DisplayAlerts = False
    On Error GoTo 1:
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Else: MsgBox ("There is a problem with the data.")
    End If
    End
    1: MsgBox ("You must have at least one visible " _
    & "worksheet in a standard .xls file")
    End Sub


    Hope this helps.

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

    Default

    This works!!! Thank you so much!

  6. #16
    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're welcome. Sorry about the confusion, took me a minute to get a handle on the process. Good hunting.

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
  •