Application-defined or object-defined error

Question

I am trying to write a function as follow:

Function getEntityCode(fileName As String) As Range
Dim entitySheet As Worksheet
Dim c As Range
Dim i As Integer
Dim valid As Boolean
Dim finalRow As Integer
Dim finalCol As Integer

Set entitySheet = Workbooks("OHUploads.xlsm").Worksheets("EntityCodes")
finalRow = entitySheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
On Error GoTo ErrHandler:
With entitySheet
   Set c = .Range(.Cells(1, 3), .Cells(1, finalCol))
End With
valid = False
ErrHandler:
Debug.Print Error(Err)
If valid = False Then
   Set getEntityCode = entitySheet.Range(entitySheet.Cells(1, 3), entitySheet.Cells(1, finalCol))
Else
   Set getEntityCode = c
End If
End Function

But no matter how do it, when the statement execute to Set c = .Range(.Cells(1, 3), .Cells(i, finalCol)) It alway end up in the errHandler, and it always shows:Application-defined or object-defined error

I thought I have explicitely defined everything, can't see where I have done wrong, can anyone help me with this please, thank you very much.

Also to note that even with an error-less execution it will still run through the ErrHandler segment. You may want to place a error-less execution method stop prior to the ErrHandler segment. And if needed, have the ErrHandler segment redirect to the closing segment. Alot of times you will see people annotate the error segment as Err_Method and the exit segment as Exit_Method, with Method being the name of the method. Helps with readability and flow control. — GoldBishop
– GoldBishop, Commented Oct 10, 2012 at 22:17

Daniel · Accepted Answer · 2012-10-10 20:08:03Z

2

You never set a value for finalCol, therefore it is defaulting to 0.

Calling Cells(1,0) throws an error because Cells is 1 based instead of 0 based. Or rather, Row or Column 0 doesn't exist.

I recommend either having a line that goes: finalCol = 1 or just use 1.

edited Oct 10, 2012 at 20:08

answered Oct 10, 2012 at 19:54

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Application-defined or object-defined error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related