error 2007

[30percent]

Hi,

I have the following code. When I run it - this line of code triggers an error:

Set rng_clients_prev = ThisWorkbook.Worksheets("Daily Totals").Range(Cells(4, col_Arr(0)), Cells(finalRow_clients, col_Arr(1)))

error message:
Run-time error '1004':
Application-defined or object-defined error

From watches window: Value of Cells(4, col_Arr(0)) is error 2007.
Not sure what what means.

I replaced col_Arr(0) and col_Arr(1) and finalRow_clients with their actual values. I still get the same error message.

Set rng_clients_prev = ThisWorkbook.Worksheets("Daily Totals").Range(Cells(4, 14), Cells(44, 36)

Wonder if someone could point out what caused the error?
Thank you

Function return_column(rng_column_loop As Range, previous_Month As Integer) As Variant


Dim cel As Range
Dim lastCol As Long
Dim firstCol As Long
Dim counter As Integer


counter = 0


For Each cel In rng_column_loop.Cells
    With cel
        If .Value = previous_Month Then
            If counter = 0 Then
                firstCol = .Column
                counter = counter + 1
            End If
            lastCol = .Column
        End If
        
    End With
Next cel


return_column = Array(firstCol, lastCol)


End Function


Sub update_Month()


Dim col_Arr() As Variant


previous_Month = current_Month - 1


col_Arr = return_column(rng_column_loop, previous_Month)


Set rng_clients_prev = ThisWorkbook.Worksheets("Daily Totals").Range(Cells(4, col_Arr(0)), Cells(finalRow_clients, col_Arr(1)))


prev_MTD_clients = WorksheetFunction.Sum(rng_clients_prev)






End Sub

 

[jasonb75]

Typo in the worksheet name maybe?

I just named a sheet as "Daily Totals" in a file that I had open and tried

Set rng_clients_prev = ThisWorkbook.Worksheets("Daily Totals").Range(Cells(4, 14), Cells(44, 36))

which worked without error.