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]

Here it is:

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 ]

[bergy]

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.

[bergy]

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

[NateO]

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

[agiammo]

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?

[agiammo]

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.

[NateO]

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.

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 ]

[agiammo]

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!

[agiammo]

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.