Printing Array of Long to Cells in VBA and getting subscript out of range error

Question

I am almost done with the spreadsheet where I compared two arrays and anything that was in one array and not in another got put into a third array.

I then want to put the values in the array to cells on a sheet of the workbook, but I get a subscript out of range even though the array shows a value in the debugger.

Here is the loop to print the array:

 If (Not MissingLoans) = -1 Then
  ThisWorkbook.Sheets("Inputs and Results").Cells(PrintCell, 1) = "No Missing Loans Found on Roll-Up"

Else
    For i = 1 To (UBound(MissingLoans())) Step 1
       *** ThisWorkbook.Sheets("Inputs and Results").Cells(PrintCell, 1).Value = MissingLoans(i)

        PrintCell = PrintCell + 1
    Next
End If

I put asterisks by the line that is giving me the out of range error, but MissingLoans(I) is showing a value. In fact Missingloans(1) is the only value in the array.

Answer 1

if there is only one value in the array, then you should access it with Missingloans(0) as arrays are 0-based.

Try this

For i = LBound(MissingLoans()) To (UBound(MissingLoans())) Step 1
   ThisWorkbook.Sheets("Inputs and Results").Cells(PrintCell, 1).Value = MissingLoans(i)
   PrintCell = PrintCell + 1
Next

Answer 2

Rather than looping the array, assign the array directly to the sheet:

Else
    Dim myRange as Range
    Set myRange = ThisWorkbook.Sheets("Inputs and Results").Cells(PrintCell, 1)

    '## Assumes Option Base 0, if Option Base 1, remove the +1
    myRange.Resize(Ubound(MissingLoans)+1).Value = Application.Transpose(MissingLoans)

    Next
End If

This code should also raise an error if the worksheet named Inputs and Results doesn't exist in ThisWorkbook, which I suspect is the real cause of your subscript out of range.

Note: I would just use Option Base 0, it's the default and most common by a long shot. Just learn to live with it. I would also begin filling the array at position 0, not 1. But if you insist on using Option Base 0 and filling it from 1..., then you'd need to do the iteration For i = 1 To UBound(MissingLoans) loop, or a ReDim Preserve on the array, either of which I think is pointlessly complicating what can be more easily done with direct array>range assignment, as per above.