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

Ken F

New Member
Joined
Mar 19, 2003
Messages
2
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!
 

Forum statistics

Threads
1,085,872
Messages
5,386,485
Members
401,999
Latest member
CROSBIE3103

Some videos you may like

This Week's Hot Topics

Top