How do I spin trhough all the data in an Excel spreadsheet using VBA 2

[SBendBuckeye]

I have used VBA in Access frequenly and have spun through the Access collections a fair amount. I have been given a very large, ugly, multisheet Excel file to convert.

Are there equivalent Excel collections that I could spin to find all of the cells with formula in them? I'm thinking of something along these lines:

Dim Whatever As Necessary

For Each Blop in MyExcelCollection
If Left$(CurrentCell, 1) = "=" 'Now we have a forumula
Save the formula and cell address
End If
Next Blop

Thanks in advance for any help you can give me!

 

[RobBroekhuis]

You can use

for each Sh in activeworkbook.sheets
for each cell in Sh.cells.specialcells(xlCellTypeFormulas)
..do things with cell (a range variable) here
next cell
next Sh

Rob

 

[SBendBuckeye]

Thanks for the quick response!

If I were in the spreadsheet as a user I would press CTL + End to get to the lower right hand corner of my spreadsheet. How do I determine which cells to check since there are 62,000 plus rows in a spreadsheet?

Thanks again!

 

[LoNeRaVeR]

SBendBuckeye:

The following code will select all the cells that contain formulae in one sheet and loop through them.

  Dim oCell As Object
  Cells.SpecialCells(xlCellTypeFormulas, 23).Select
  For Each oCell In Selection
    MsgBox oCell.Address & "   " & oCell.Formula
  Next oCell

I only provided the

MsgBox

code to show how to retrieve the address and formula. I suggest you either step through or remove it before you run the program lest you find yourself swamped with message boxes.

Regards, LoNeRaVeR

 

[LoNeRaVeR]

SBendBuckeye:

Both RobBroekhuis and my examples select only those that contain formulae. RobBroekhuis's example will traverse all the sheets in the workbook.

Regards, LoNeRaVeR

 

[SBendBuckeye]

Thanks to both of you. That is exactly the type of thing I was looking for. I knew it would probably be pretty simple, but I didn't know where to start.

Have a great day!