excel VBA End method on Range object -- application-defined or object-defined error

Question

I'm experiencing an error with using the End method of the range object.

The code below is an excerpt from my program, and when I get to the last line I get the following error: "Application defined or Object Defined error"

Sub DataGroup(testCell, j, k)
    Dim archive As Range
    Set dataCellsA = Worksheets("DATA").Range("B2")
    Set dataCellsB = Worksheets("DATA").Range("I2")
    For i = 0 To 30
        Set currentLine = testCell.Offset(i, 0)
        Set archive = Worksheets("ARCHIVE").Range("B4").End(x1Down)

However, in a later portion of my program I have what appears to be the same implementation, but I don't receive any errors with this portion.

    Set dataCellsA = Worksheets("DATA").Range("C2")
    Set dataCellsB = Worksheets("DATA").Range("J2")

    Dim lastCellA As Range
    Dim lastCellB As Range

    Set lastCellA = dataCellsA.End(xlDown)
    Set lastCellB = dataCellsB.End(xlDown)

Whats the difference between the two? I'm making sure to declare my variables as ranges, and I'm careful to use the "Set" prefex to ensure that it is an object, and not the value.

Is "ARCHIVE" correctly spelled? And is it in the same workbook as the code? — Daniel Möller
– Daniel Möller, Commented Mar 26, 2013 at 20:27
@KazJaw care to change it into an answer and get this question "resolved"? — K_B
– K_B, Commented Mar 26, 2013 at 22:15
@KazJaw Oh wow.. thanks for that one. I would have never noticed such a small detail — user1860694
– user1860694, Commented Mar 26, 2013 at 22:40

Kazimierz Jawor · Accepted Answer · 2013-03-26 22:21:00Z

2

So, here is the answer: you have x1Down where you should have xlDown (1 instead of l)

answered Mar 26, 2013 at 22:21

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

excel VBA End method on Range object -- application-defined or object-defined error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related