Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi all,

    I wanted to ask, can someone see the error with this code, I keep getting an application error, object not set.....


    Thanks,
    Darryl.





    Option Explicit
    Public Function do_the_Birthday() As Integer
    Dim count As Integer
    Dim i As Integer
    Dim bc As Integer
    Dim lo As Integer
    Dim counthewhat As Range
    Dim days As Variant
    Set counthewhat = Worksheets(1).Range(Cells(4, 11), Cells(4, 288)) ' This is where the error happens.
    With counthewhat
    days = Application.WorksheetFunction.Sum(counthewhat)
    End With
    do_the_Birthday = days

    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Because you don't specify which worksheet Cells(4, 11) and Cells(4, 288) are on, VBA assumes they are on the active sheet. But if Worksheets(1) is not the active sheet, this causes an error, for you can't have a range on Worksheets(1) that begins and ends with a cell on another sheet. Change the line

    Set counthewhat = Worksheets(1).Range(Cells(4, 11), Cells(4, 288)) ' This is where the error happens.

    to

    Set counthewhat = Worksheets(1).Range(Worksheets(1).Cells(4, 11), Worksheets(1).Cells(4, 288))

    This explicitly tells VBA that the cells are on Worksheets(1).

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Hans,

Posting Permissions

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