CODEWITHSUNDEEP
arraysexcelvba
PhilT41
PhilT41

Reputation: 99

Print Array result from For Each getting subscript out of range error

I need to create an array from my for each statement and print it to a file.

I would leave out the array and print from within the for each loop (which works) but I need to add to the contents of the file and then also access the array later from a different function (a question for another time).

Public Function Orders()
    Dim ItemDescription As String, Counter As Integer, Range As Range, sArray() As Variant
    ReDim sArray(1 To 1) As Variant
    length = Cells(Rows.Count, 1).End(xlUp).Row
    Set Range = ActiveSheet.Range("A2:A" & length)
    IntFile = FreeFile
    StrFile = "C:\Projects\Test.txt"
    Open StrFile For Output As #IntFile
    For Each Cell In Range
        ItemDescription = Cell.Offset(0, 19).Value
            If (Cell.Value = Cell.Offset(1, 0).Value And Cell.Value <> Cell.Offset(-1, 0).Value) Or (Cell.Value <> Cell.Offset(-1, 0).Value And Cell.Value <> Cell.Offset(1, 0).Value) Then
                Counter = 1
            ElseIf (Cell.Value = Cell.Offset(1, 0).Value And Cell.Value = Cell.Offset(-1, 0).Value) Or (Cell.Value <> Cell.Offset(1, 0).Value And Cell.Value = Cell.Offset(-1, 0).Value) Then
                Counter = Counter + 1
            End If
            If Counter = 1 Then
                OlStr = ItemDescription
            ElseIf Counter > 1 Then
                 OlStr = ItemDescription & " " & "your count =" & " " & Counter
            End If
            sArray(UBound(sArray)) = OlStr
            ReDim Preserve sArray(1 To UBound(sArray) + 1) As Variant
    Next Cell
    Print #IntFile, sArray(1, UBound(sArray))
    Close #IntFile
End Function

I receive a subscript out of range error at Print #IntFile, sArray(1, UBound(sArray)).
enter image description here

I understand that this means I am trying to reference an item outside the dimension of the array, but I can't figure out why. If I put the Print line inside the For Each I still get the error.

It looks like UBound is created correctly. I have 2537 lines in my worksheet, though it does have a header.

enter image description here

Upvotes: 0

Views: 54

Answers (1)

DecimalTurn
DecimalTurn

Reputation: 4129

As per my comments:

You have a one dimensional array so you can only specify one index value. sArray(1) and sArray(UBound(sArray)) would be valid but not sArray(1, UBound(sArray))

If you just want to print all the cell values, you could put Print #IntFile, sArray(UBound(sArray)) inside the For Each loop before the Redim Statement. Or, you could just bypass the array an do Print #IntFile, OlStr.

Upvotes: 0

Related Questions