worksheet function

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

Thanks Hans,

Reply | Quote