Advanced FIND technique

[agiammo]

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?

 

[NateO]

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-14 09:04

 

[NateO]

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

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.

 

[agiammo]

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

 

[NateO]

Try replacing your last three sections with:

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

Hope this helps.

 

[agiammo]

This works!!! Thank you so much!

 

[NateO]

You're welcome. Sorry about the confusion, took me a minute to get a handle on the process. Good hunting.