Sum funtion returns error 13 incompatible data type

[mtenorio]

Hello!

I am trying to fetch data from a text file. After extracting the needed info on the file, I converted the quantity from a string to a number.

I would like to add all of the quantities, so I used the SUM function in excel VBA. However, I am getting an incompatible data type error for the sum function when the data lines reach a very large number ( around 10,000).

Can you help me on this? Below is the code I am using.

'Code for the previous month
If FileName(a, d, b) = "" Then
    Range("I7").Value = "NO DATA"
    MsgBox "There is no file found for the previous month."
Else
    Open FileName(a, d, b) For Input As #1                            'opens the sales file for the previous month
    row_number = 0
    Do Until EOF(1)                                                          'reads the text line per line until the end of file
        Line Input #1, LineFromFile
        product_previous = Mid(LineFromFile, 104, 80)                         'extracts the product name
        quantity_previous = Mid(LineFromFile, 237, 10)                        'extracts the quantity
        product_previous_all = product_previous_all & product_previous & ";"    'stores the product name into an delimited array
        quantity_previous_all = quantity_previous_all & quantity_previous & ";" 'stores the quantity into an delimited array
        row_number = row_number + 1
    Loop
    Close #1
    'Splits the extracted string
    stext_previous() = Split(product_previous_all, ";")
    snumber_previous() = Split(quantity_previous_all, ";")
    
    For I = LBound(snumber_previous) + 1 To UBound(snumber_previous) - 2
        ReDim Preserve SrtTemp_previous(I)
            'MsgBox snumber_previous(I)
            SrtTemp_previous(I) = CLng(snumber_previous(I))
            Debug.Print TypeName(SrtTemp_previous(I))
            'MsgBox SrtTemp(I)
    Next I
    
   [COLOR=#ff0000][B] Range("I7").Value = WorksheetFunction.Sum(SrtTemp_previous())[/B][/COLOR]
    
    top10_previous(1) = WorksheetFunction.Large(SrtTemp_previous(), 1)
    top10_previous(2) = WorksheetFunction.Large(SrtTemp_previous(), 2)
    top10_previous(3) = WorksheetFunction.Large(SrtTemp_previous(), 3)
    top10_previous(4) = WorksheetFunction.Large(SrtTemp_previous(), 4)
    top10_previous(5) = WorksheetFunction.Large(SrtTemp_previous(), 5)
    top10_previous(6) = WorksheetFunction.Large(SrtTemp_previous(), 6)
    top10_previous(7) = WorksheetFunction.Large(SrtTemp_previous(), 7)
    top10_previous(8) = WorksheetFunction.Large(SrtTemp_previous(), 8)
    top10_previous(9) = WorksheetFunction.Large(SrtTemp_previous(), 9)
    top10_previous(10) = WorksheetFunction.Large(SrtTemp_previous(), 10)
    
    Worksheets("Sheet1").Activate
    
    Range("M12").Value = top10_previous(1)
    Range("M13").Value = top10_previous(2)
    Range("M14").Value = top10_previous(3)
    Range("M15").Value = top10_previous(4)
    Range("M16").Value = top10_previous(5)
    Range("M17").Value = top10_previous(6)
    Range("M18").Value = top10_previous(7)
    Range("M19").Value = top10_previous(8)
    Range("M20").Value = top10_previous(9)
    Range("M21").Value = top10_previous(10)
    
    
    For l = 1 To 10
        For k = LBound(SrtTemp_previous()) To UBound(SrtTemp_previous())
            If top10_previous(l) = SrtTemp_previous(k) Then
                Worksheets("Sheet1").Activate
                Range("K" & l + 11).Value = stext_previous(k)
            Else
            End If
        Next k
    Next l
        
End If

 

[Norie]

What data type(s) are the values in the array you are trying to sum?

 

[mtenorio]

What data type(s) are the values in the array you are trying to sum?

Hi Norie. The data type is Long. I have converted the string text to a number. Please see the code I have used for the conversion.

For I = LBound(snumber_previous) + 1 To UBound(snumber_previous) - 2
        ReDim Preserve SrtTemp_previous(I)
            'MsgBox snumber_previous(I)
            SrtTemp_previous(I) = CLng(snumber_previous(I))
            Debug.Print TypeName(SrtTemp_previous(I))
            'MsgBox SrtTemp(I)
    Next I

 

[Norie]

No nulls in the array?

 

[mtenorio]

No nulls in the array?

Does null mean empty? I am not sure if there are any nulls on the array... Would you know how to check for this?