Hi Paul,
I think the op means that the text "NO SALES" can be in any cell somewhere on
the sheet - sheet name unknown, and not a sheet called "NO SALES".
Hence his desire to look thru every cell on every sheet. (Cells.Select -
Selection.Find)
And that's a good tip about counting backwards. I've come across that myself
when working down thru a bunch of rows, deleting some according to specific
criteria. I found a solution, but not as elegant as just working up from the
bottom.
Regards - Dave.
Date: Thu, 28 May 2009 04:43:38 -0700
From: schreiner_p...@att.net
Subject: $$Excel-Macros$$ Re: Trouble with find
To: excel-macros@googlegroups.com
you're off to a good start.
However, the .Find function looks for things in the selected CELL.
What you really want is to see if the sheet NAME is "No Sales".
Try:'==========================================================
Sub test()
for i = 1 to Sheets.Count
if (ucase(Sheets(i).Name) = "NO SALES") then
Sheets(i).Delete
Exit For
end If
Next I
End Sub
'==========================================================
Notice the "Exit For".. well, there can be only one sheet called "No Sales",
so once it's gone, there no use in continue looking!
Now, what if you expect sheets that CONTAIN the name "No Sales"
(like "No Sales 1", "No Sales 2")
then I'd use:
'==========================================================
Sub test()
for i = Sheets.Count to 1 step -1
if (instr(1,ucase(Sheets(i).Name),"NO SALES") > 0) then
Sheets(i).Delete
end If
Next I
End Sub
'==========================================================
Notice that I used Sheets.Count to 1 instead of 1 to Sheets.Count.
That is because:
Let's say that there are 10 sheets.
The third sheet is called "No Sales"
If you loop from 1 to sheets.count, then when you get to
I = 3, sheet 3 is deleted, sheet 4 then becomes sheet3, (sheet 5 becomes 4, etc)
so the loop would then SKIP checking what was sheet 4.
Then, when it got to i=10, it would ERROR, because there are not 10 sheets!
by counting down, it eliminates this problem.
hope this helps.
Paul
From: sjsean <sjsean95...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Wednesday, May 27, 2009 8:00:27 PM
Subject: $$Excel-Macros$$ Trouble with find
I have a set of worksheets where the data provider always puts a sheet
with "No Sales".
I am trying to write a macro that will remove this sheet (and others
if they fit the above criteria).
Sub test()
For i = 1 To Sheets.Count
Sheets(i).Select
Cells.Select
Dim r As Range
Set r = Selection.Find(What:="No Sales", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate
If Not r Is Nothing Then Sheets(i).Delete
Next
End Sub
_________________________________________________________________
Looking to move somewhere new this winter? Let ninemsn property help
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline&_t=774152450&_r=Domain_tagline&_m=EXT
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
