Advanced FIND technique

agiammo

Board Regular
Joined
May 9, 2002
Messages
68
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?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-10 15:22
 
Upvote 0
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.
 
Upvote 0
Bergy, you were right on target, your code did a little more than mine, looping through each sheet. Have a great weekend all.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-14 08:57
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top