Excelpromax123
Board Regular
- Joined
- Sep 2, 2021
- Messages
- 172
- Office Version
- 2010
- Platform
- Windows
Hello everyone. I have used the code to accumulate the quantity by item code. Code is wrong when adding item code "AAA", the correct result is 0 (-11.67 + 11 + 0.67 = 0 ). But the result of running code is 1.11022E-16 . Thanks everyone for fixing the above error. Sincerely thank
VBA Code:
Sub sumtotal()
On Error Resume Next
Dim Dic As Object, dArr As Variant, arr As Variant, Tmp As Variant, s As Variant
Dim i As Long, J As Integer, K As Long, ik As Long, key As String, b As Long
Set Dic = CreateObject("Scripting.dictionary")
dArr = Range("b4:b100").Value
ReDim arr(1 To UBound(dArr), 1 To 3)
For i = 1 To UBound(dArr)
Tmp = Split(dArr(i, 1), ";")
For J = LBound(Tmp) To UBound(Tmp)
s = Split(Tmp(J), "*")
If UBound(s) = 2 Then
key = s(0)
If Not Dic.Exists(key) Then
K = K + 1
Dic.Add key, K
arr(K, 1) = key
End If
ik = Dic.Item(key)
arr(ik, 2) = arr(ik, 2) + CDbl(s(1))
arr(ik, 3) = arr(ik, 3) + CDbl(s(2))
End If
Next J
Next i
Range("D4:F1100").ClearContents
Range("D4:F1100").Resize(K) = arr
End Sub
fix vba Sum.xlsx
Sheet1 syntax: Name1*Qty1*Qty2;Name2*Qty2*Qty2;…. Input,Ouput Text,name,QTY 1,QTY 2, The item code is "AAA" the correct result should be 0. But the code is calculated as 1.11022E-16 AAA*-11.67*789;,AAA,0,1491 AAA*11*457;,B,34,34,861 AAA*0.67*245;,C,18,47 B*-12.67*79;,DD,0,735 B*18*47;C*18*47 B...
docs.google.com