利用VBA统计指定项目数量

ba3259878266

利用VBA统计指定项目数量  谢谢各位老师帮忙  

wengjl

你的问题会正则的解决比较容易，不会正则也可以解决，但代码太烦啦！

无关岁月

1. Sub 按钮1_Click()
   
2. Dim arr, i, j, m, sum
   
3. arr = [d2:e8]
   
4. ReDim brr(1 To UBound(arr) * 2, 1 To 1)
   
5. For i = 1 To UBound(arr)
   
6.     For j = 1 To UBound(arr, 2)
   
7.         If InStr(arr(i, j), "槟榔") Then
   
8.             m = m + 1
   
9.             brr(m, 1) = Val(Mid(arr(i, j), InStr(arr(i, j), "槟榔") + 2))
   
10.          End If
    
11.     Next
    
12. Next
    
13. For i = 1 To m
    
14.     sum = sum + brr(i, 1)
    
15. Next
    
16. [f1] = sum
    
17. End Sub

复制代码

ba3259878266

无关岁月 发表于 2024-9-3 06:53

谢谢老师 很好用  满意  谢谢

Qs18

Sub qs()
Dim arr, i
arr = Sheet1.[d2:e8].Value
For Each a In arr
    If InStr(a, "槟榔") And a <> Empty Then
        ar = Split(a, "槟榔")
        For Each b In ar
            sm = sm + Val(b)
        Next
    End If
Next
[f1].Value = sm
End Sub

Qs18

试试.............

Qs18

试试...........

ykcbf1100

参与一下，正则

ykcbf1100

正则统计

1. Sub ykcbf()  '//2024.9.3   正则统计
   
2.     Dim st As String
   
3.     arr = [D2:E8]
   
4.     For Each k In arr
   
5.         If k <> Empty Then
   
6.             st = k
   
7.             count = count + CountBanlangNumber(st)
   
8.         End If
   
9.     Next
   
10.     [f1] = count
    
11.     MsgBox "OK！"
    
12. End Sub
    
13. 
    
14. Function CountBanlangNumber(text As String) As Integer
    
15.     Set reg = CreateObject("VBScript.RegExp")
    
16.     With reg
    
17.         .Global = True
    
18.         .Pattern = "槟榔(\d+)"
    
19.         .IgnoreCase = True
    
20.     End With
    
21.     count = 0
    
22.     Set mh = reg.Execute(text)
    
23.     For Each ma In mh
    
24.         count = count + Val(ma.SubMatches(0))
    
25.     Next ma
    
26.     CountBanlangNumber = count
    
27. End Function
    

复制代码