CODEWITHSUNDEEP
excelvba
Unfundednut
Unfundednut

Reputation: 334

Excel VBA Subscript Out Of Range on 00:00 Split

I am looping through a table in Excel with VBA to split the value into the next column. The table looks like this.

+---------------------+---------------------+
| Interval Start Date | Interval Start Time |
+---------------------+---------------------+
| 2019-07-01 04:00    |                     |
| 2019-07-01 05:00    |                     |
| 2019-07-01 05:00    |                     |
| 2019-07-01 05:00    |                     |
| 2019-07-01 06:00    |                     |
+---------------------+---------------------+

The first column is A the second is B. I am using this code that I have written/acquired.

Sub SplitCells()
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row).Copy Destination:=Range("B2")
    Dim rLastRow As Range
    Dim row As Integer
    Set ws = Sheets("Monthly Queue activity by hour-")
    row = 2
    Set rLastRow = Cells(Rows.Count, "B").End(xlUp)
    rLastRow = rLastRow - 1

    Dim TestArray As Variant
    With ws
        Do
            TestArray = Split(CStr(.Cells(row, 1).Value))
            .Cells(row, 2) = TestArray(1)
            row = row + 1
        Loop Until row = rLastRow
    End With
End Sub

It will give me the Subscript Out Of Range if the timestamp I am splitting out is 00:00. I have tried searching and I can't seem to track down how to fix this.

I remove the entries with the 00:00 timestamp and it works, except I still get the Out Of Range when it reaches the end.

Upvotes: 0

Views: 117

Answers (1)

TheJeebo
TheJeebo

Reputation: 166

Since rLastRow is a range you need to call the .row property to end the Loop. This with the .Text suggestion should do the trick.

Sub SplitCells()
Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row).Copy Destination:=Range("B2")
Dim rLastRow As Range
Dim row As Integer
Set ws = Sheets("Monthly Queue activity by hour-")
row = 2
Set rLastRow = Cells(Rows.Count, "B").End(xlUp)
rLastRow = rLastRow - 1

Dim TestArray As Variant
With ws
    Do
        TestArray = Split(CStr(.Cells(row, 1).Text))
        .Cells(row, 2) = TestArray(1)
        row = row + 1
    Loop Until row = rLastRow.row + 1
End With

End Sub

Upvotes: 2

Related Questions