How can a variable for sheet be used to reference a cell

[Ken F]

This is a spinoff of the original thread: "Macro to add numbers from different worksheets (Excel 2000)"

I got GREAT answers how to use a 3d formula in contiguous sheets, but I can't guarantee I know the names of the first and last sheets and I can't guarantee they will be contiguous.

So I am trying to write a macro to total all sheets except those with a certain name as shown below. When I try to access cell B7 on sheet s, I get a compile error on two lines that says "Object Required". What am I doing wrong? Here is the macro:

a = 0
b = 0
For Each s In Worksheets
If s.Name <> "Directories" Then
If s.Name <> "Summary" Then
b = b + s.Name!B7 <-----------PROBLEM HERE
a = a + (s.Name!B7 / s.Name!B10) <--------AND HERE
End If
End If
Next s
Range("B7").Select
ActiveCell.FormulaR1C1 = b
Range("B10").Select
ActiveCell.FormulaR1C1 = a

THANKS!

 

[Andrew Poulsom]

Try:

b = b + s.Range("B7").Value
a = a + (s.Range("B7").Value / s.Range("B10").Value)