Excell VBA Runtime Error '1004': Application-defined or object-defined error

Question

I'm trying to write this code to assist in data analysis for my dissertation but i get this error at a particular line:

Set RankValue = Sheets(1).Range("D2:D & LastRow")

I'm new to VBA but i have tried all options to present the right syntax as can be in the code below.

Private Sub Timeframe()

Dim LastRow As Long
Dim Timeframe As New Collection
Dim RowNumber As Long
Dim RankValue As Range
Dim Cell As Range
Dim Item As Variant

    LastRow = Sheet1.Cells(Rows.Count, 4).End(xlUp).Row

    Debug.Print LastRow

    Sheet1.Range("F1").Value = "Timeframe"

    Sheet1.Range("F2:F" & LastRow).Clear

    RowNumber = 1

    'Set RankValue = Sheet1.Range("D2:D")

    'Set RankValue = Sheet1.Range("D2:D & LastRow")

    'Set RankValue = Application.Workbooks("LoopCode2.xlsm").Worksheets("Sheet1").Range("D2:D & LastRow")

    'Set RankValue = Application.Workbooks("LoopCode2.xlsm").Worksheets("Sheet1").Range("D2:D")

    'Set RankValue = Worksheets("Sheet1").Range("D2:D & LastRow")

    'Set RankValue = Sheets("Sheet1").Range("D2:D & LastRow")

    'Set RankValue = Sheets("Sheet1").Range("D2:D")

    'Set RankValue = Sheets("Sheet1").Range("D2:D & LastRow")

    'Set RankValue = Worksheets(1).Range("D2:D & LastRow")

    Set RankValue = Sheets(1).Range("D2:D & LastRow")


    For Each Cell In RankValue

        On Error Resume Next

        Timeframe.Add Item:=Cell.Value, Key:=CStr("Cell.Value")

        On Error GoTo 0

    Next Cell

    Debug.Print LastRow


    For Each Item In Timeframe

        RowNumber = RowNumber + 1

        Sheet1.Cells(RowNumber, 6) = Item.Value

    Next Item

    Set Timeframe = New Collection

End Sub

Grateful if you could assist me find what I'm doing wrong, and how can i fix it.

Sam · Accepted Answer · 2019-03-07 13:23:48Z

1

Replace your line with this:

Set RankValue = Sheets(1).Range("D2:D" & LastRow)

The ("D2:D" & LastRow) part builds an address, like D2:D45. & means concatenation

Added after comments
If you put this just before a failing statement, you will understand more:

Debug.Print "D2:D" & LastRow
Set RankValue = Sheets(1).Range("D2:D" & LastRow)

Press Ctrl+G to show the debug window. It will print the value of "D2:D" & LastRow which is expected to be a range. If LastRow is empty or contains a string value, your range will normally not be valid.

edited Mar 7, 2019 at 13:23

answered Mar 7, 2019 at 0:37

Sam

5,7411 gold badge21 silver badges36 bronze badges

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

6 Comments

mkb123 Over a year ago

Please I used 'Set RankValue = Sheets(1).Range("D2:D")' and had the same error message. In fact, the various combinations i have used already are shown in the code, please. Thanks,

mkb123 Over a year ago

Please, it is meant to be a concatenation. It has been used earlier in the code successfully, so i don't think that is the problem, please.

mkb123 Over a year ago

You're right Sam, I used 'Set RankValue = Sheets(1).Range("D2:D" & LastRow)' and it went through successfully without the error message.

mkb123 Over a year ago

ou're right Sam, I used 'Set RankValue = Sheets(1).Range("D2:D" & LastRow)' and it went through successfully without the error message. But somehow, the next step did not go through successfully - that's only the first data item was added to the Collection. I'm investigating that to ensure that it has no relation with the changed line. Please, is there a reason why 'Range("D2:D")' and 'Range("D2:D & LastRow")' produce that error message? i think the data has not been selected into the collection as expected. I'm also not sure the syntax for the next step is fully accurate. Working on it

Sam Over a year ago

I have added some more to the answer

|

Collectives™ on Stack Overflow

Excell VBA Runtime Error '1004': Application-defined or object-defined error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related