SUM Function in VBA: How to sum when array contains non-numeric characters

[mtenorio]

Hi guys!

I am using the SUM function in excel vba to sum an array. However, I am having an error when there's non-numeric parts of the array. Is there a way to ignore the non-numeric parts and just sum the numeric parts?

Thanks a lot in advance!

 

[Scott Huish]

Works fine for me:

Sub test()
Dim x
x = Array(1, 7, "A", 5)
MsgBox Application.WorksheetFunction.Sum(x)
End Sub

Can you give some more specifics, maybe post your code?? Samples of what you are trying to sum?

 

[mtenorio]

Works fine for me:

Sub test()
Dim x
x = Array(1, 7, "A", 5)
MsgBox Application.WorksheetFunction.Sum(x)
End Sub

Can you give some more specifics, maybe post your code?? Samples of what you are trying to sum?

Here is my code:

'Code for the previous month
Dim stext_current() As String, stext_previous() As String
Dim row_number As Long, LineFromFile As String
Dim a As String, b As String, c As String, d As String, file_current As String, file_previous As String
Dim product_current As String, quantity_current As String, quantity_m2 As String
Dim product_previous As String, quantity_previous As String
Dim product_current_all As String, quantity_current_all As String, snumber_current() As String, quantity_m2_all As String
Dim product_previous_all As String, quantity_previous_all As String, snumber_previous() As String, snumber_m2() As String
Dim SrtTemp_current() As Long, top10_current(1 To 10) As Long, SrtTemp_m2() As Long
Dim SrtTemp_previous() As Long, top10_previous(1 To 10) As Long
Dim sum_current As Long, sum_previous As Long, sum_month2 As Long, sum_month3 As Long, sum_m12 As Long
Dim period_m2 As String, period_m3 As String, period_m12 As String
Dim quantity_m3 As String, quantity_m3_all As String, snumber_m3() As String, SrtTemp_m3() As Long
Dim quantity_m12 As String, quantity_m12_all As String, snumber_m12() As String, SrtTemp_m12() As Long
Dim I As Long
Dim j As Long
Dim k As Long
Dim l As Long

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
    
    [B][COLOR=#ff0000]Range("I7").Value = WorksheetFunction.Sum(SrtTemp_previous())[/COLOR][/B]
    
    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

The raw files sometimes have values like x = (-10000P, 10000, 200, 10-000,...). I would like to ignore the non-numeric parts and just sum the numeric parts..

 

[Scott Huish]

Assuming that the -10000P should be adding -10000 and the 10-000 should be adding 10 and so on. When you are reading in the values use the VAL function.

Here is a simple example:

Msgbox Val("-10000P")

 

[mtenorio]

Hello Scott, I want to ignore those alphanumeric characters.

I know now why I am having an error. When I used Clng on an alphanumeric character, it results in a null value.

So when I used the sum function, I got an error.

For this, I just used #On Error Resume Next" to fix the problem.

Thanks a lot anyway!

 

[Scott Huish]

I would use a check to see if it can be evaluated as a number rather than On Error Resume Next

If IsNumeric(SrtTemp_previous(I)) Then SrtTemp_previous(I) = CLng(SrtTemp_previous(I))

 

[mtenorio]

I would use a check to see if it can be evaluated as a number rather than On Error Resume Next

If IsNumeric(SrtTemp_previous(I)) Then SrtTemp_previous(I) = CLng(SrtTemp_previous(I))

Hi Scott, this code is very good! Prevents me from having null values. Thanks a lot! :D