Can I list the worksheets in an Excel workbook? 2

[VoodooRage]

I am writing some code for a spreadsheet that will need to search each sheet in the workbook. I am not terribly familiar with the Excel object model or syntax but have done tons of VBA for Access. I will need to start at the first worksheet in the workbook and search, then move to the next worksheet and search each sheet. I have the code that will search and replace the portion of the text string that I am looking for but for this to work efficiently I need to cover the entire workbook for accuracy.

Thanks, you all rock!

 

[mwolf00]

sheetCount = Application.Sheets.Count

for x = 1 to sheetCount
sheets(x).cell(1,1).value = x
next -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[origapizni]

If you want to replace the text in the same range of cells on all the sheets at one time instead of stepping thru them (same as selecting all the tabs at once), this will work and might be faster:

Sub ReplaceOnAllSheetsAtOnce()

Dim SheetList() As String
x = ActiveWorkbook.Worksheets.Count
ReDim SheetList(x)
For i = 1 To ActiveWorkbook.Worksheets.Count
SheetList(i) = Worksheets(i).Name
Next i
Sheets(SheetList).Select
Sheets(1).Activate
Range("A2&quot.Select ''' put your range here
Selection.Replace What:="test", Replacement:="boo", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

Of course, if you're searching different ranges on each sheet, then this might not be what you need.

 

[SBendBuckeye]

As above, I am just starting to get my feet wet with the Excel object model. What would be the code to spin through all of the active cells in a worksheet. You don't actually need to spin through everything from A-IV and 1 to 62,000 whatever do you?

Thanks for the help!

 

[RobBroekhuis]

I think you'll find that one of the following references will do what you're trying to accomplish (which one depends on your application):

* the (range).specialcells property can find all cells of a certain type (e.g., those containing formulas)
* the (worksheet).usedrange property returns the range that includes all used cells on the worksheet. Not all of those cells are necessarily used: the bottom right cell of this range is the highest used row and the highest used column.

For more information you'll find the VBA help topics on these subjects to be quite useful.
Rob

 

[VoodooRage]

Thanks for all of your help everyone. Your suggestions were a great help.

Merry Christmas!

 

[origapizni]

In reply to SBendBuckeye's question, the tips from RobBroekhuis are important to know. In addition, it's important to design code that avoids looping (For Each ... Next) one-at-a-time thru every cell you might want to change.

The reason is that with large numbers of cells, this can become extremely slow and your code can run many minutes, or even hours. Instead, try to take advantage of the fact that many VBA Methods will operate on all cells in a range at once, like the .Replace method in the example above.

If you need to do a task that Excel can only do one cell at a time, then often you can copy the data into a VBA array and process it in memory, then 'paste' the results back to the worksheet. This sounds like advanced programming, but once you do it a couple of times, it really turns into a very simple thing to do.

As an example of the dramatic improvements you can get, using these techniques I recently reduced an 8-hour process to about 20 seconds. If anyone has seriously slow code, look for For...Next loops as the possible culprits. Post them if you want tips on possible faster alternatives.

Look for books by Eric Wells and John Walkenbach for many good tips on this sort of thing.