Excel vba - Split function gives 'Subscript out of range' error inside loop

Question

I have two columns with data. Each cell is formatted: "***" or "XXX at mm.dd.yyyy", where XXX represent various numeric combinations, and I need to replace "XXX at mm.dd.yyyy" with "XXX", so I've done this:

For Each c In Range(.Cells(2, 9), .Cells(finalrow, 10))
        If c <> "***" Then
            c.Value = Split(c, " at")(0) * 1
        End If
Next c

but I get a 'Subscript out of range' error on the row 2345.

What am I missing here?

Might be a blank cell, or value that doesn't fin into "***" format but doesn't have at delimitier. The problem is that that array you create doesn't have any items at all. Temporary fix is to add condition If Ubound(Split(c, " at") >=0 Then — AntiDrondert
– AntiDrondert, Commented Dec 19, 2017 at 10:02
Yes, that's it, there was a blank cell in the row 2330. It's my mistake. — Creakush Coliko
– Creakush Coliko, Commented Dec 19, 2017 at 10:05
@CreakushColiko so row 2345 actually meant your row in Excel? I am impressed. — Vityata
– Vityata, Commented Dec 19, 2017 at 10:07
Welcome! Please check out the tour and also see How to Ask as well as minimal reproducible example prior to making an edit to your question or before your next post. Thanks! — ashleedawg
– ashleedawg, Commented Dec 19, 2017 at 10:09

Vityata · Accepted Answer · 2017-12-19 10:11:10Z

5

Concerning that you probably get an error because of the empty 2345 row:

For Each c In Range(.Cells(2, 9), .Cells(finalrow, 10))
        If c <> "***" Then
            If InStr(1, c, " at ") Then
                c.Value = Split(c, " at")(0)
            End If
        End If
Next c

It checks whether there is " at " in c, thus the split will not result in an error.

edited Dec 19, 2017 at 10:11

answered Dec 19, 2017 at 10:04

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

Collectives™ on Stack Overflow

Excel vba - Split function gives 'Subscript out of range' error inside loop

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