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.
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:
'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