Out of Range Question

[tomgrandy]

This is the same macro that I've run on spreadsheets with 500 artifacts from a sale, but this particular one is being run on 68,000 rows of artifact sales and I'm getting an Out of Range error and I'm not sure why.

Sub URLtoImage()
Dim i As Long
Dim c As Range
Dim tx As String
Dim va, ary

    Application.ScreenUpdating = False

va = Range("A3", Cells(Rows.Count, "A").End(xlUp))
tx = "http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/AUCTION_DATE/"
For i = 1 To UBound(va, 1)
    ary = Split(va(i, 1), "/")
    va(i, 1) = tx & ary(UBound(ary))
Next
Range("I3").Resize(UBound(va, 1), 1) = va
Range("J3").Resize(UBound(va, 1), 1) = va
Range("K3").Resize(UBound(va, 1), 1) = va
Range("L3").Resize(UBound(va, 1), 1) = va


    Application.ScreenUpdating = True
    
    MsgBox "URL to Image Macro complete!"
    
End Sub

 

[6StringJazzer]

Which line of code is raising the error? When the error occurs, what are the values of all of the variables in that line of code?

 

[tomgrandy]

This is the line of code that is raising the error:

va(i, 1) = tx & ary(UBound(ary))

What I am unsure is if the va is referring to column i row 1. If so, that would not be correct as the true value begins on row 3.

Admitting to be a beginner here.

Thanks!
Tom

 

[tomgrandy]

If I only copy over 1,000 items, it runs like a charm with no errors.

What I don't want to do is create 68 separate spreadsheets to run the same macro on.

Is there a maximum number of rows built into Excel where a macro can only run up to?

Thanks!
Tom

 

[Alex Blakenburg]

What do you want to return if a cell in column A is blank ?
Try this:

For i = 1 To UBound(va, 1)
    If va(i, 1) <> "" Then
        ary = Split(va(i, 1), "/")
        va(i, 1) = tx & ary(UBound(ary))
    End If
Next

 

[tomgrandy]

@Alex Blakenburg - Thanks for helping me avoid the blanks! That worked and also encouraged me to go through and find the cells in column A that were blank.

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.