Results 1 to 2 of 2

Thread: How can a variable for sheet be used to reference a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2003
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default

    Try:

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •