Excel VBA: Runtime Error 424, Object Required

Question

I can't figure this issue out. I had a similar problem before, it turned out I was using a Range of a single cell, which was redundant. But in this case I need End(xlDown) and I can't get it to work. I tried a few combinations and I can't figure out the right syntax. Help?

Public Exceptions As Range
Public Xcept As Range

Sub Example()
Static ExcSh As Worksheet
Set ExcSh = Worksheets("ComboExceptions")
Set Exceptions = ExcSh.Range("A2")

Set Xcept = ExcSh.Range(Exceptions.Offset(1).Cells, Exceptions.Offset(1).Cells.End(xlDown))
'This is where the error happens ^
End Sub

Is there a reason you just didn't start in A3 instead of A2 then .Offset(1) ? — user4039065
– user4039065, Commented Feb 22, 2016 at 12:24
There is, a very good reason yes. Either way, I solved it. Will post an answer right now. — Viktor Zafirovski
– Viktor Zafirovski, Commented Feb 22, 2016 at 12:25

Viktor Zafirovski · Accepted Answer · 2016-02-22 12:26:54Z

1

Solved it! I had declared

Static ExcSh As Worksheet

in another Sub making it inaccessible to the function that errored. I made it public and now the following command works fine:

Set Xcept = ExcSh.Range(Exceptions.Offset(1), Exceptions.Offset(1).End(xlDown))

answered Feb 22, 2016 at 12:26

Viktor Zafirovski

4371 gold badge5 silver badges15 bronze badges

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

1 Comment

Ralph Over a year ago

As per @Jeeped comment above Set Xcept = ExcSh.Range(Exceptions, Exceptions.End(xlDown)) would also suffice if you adapt your starting point.

Collectives™ on Stack Overflow

Excel VBA: Runtime Error 424, Object Required

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related