利用VBA统计出D列各行的总数

无关岁月

自定义函数

1. Public Function Sumstr(arr)
   
2. Dim rng, reg, i&, m, sum
   
3. Set reg = CreateObject("vbscript.regexp")
   
4. With reg
   
5.     .Global = 1
   
6.     .Pattern = "(\-)?\d+(\.\d{1,})?"
   
7.     For Each rng In arr
   
8.         Set m = .Execute(rng)
   
9.         For Each k In m
   
10.             sum = sum + CDbl(k.Value)
    
11.         Next
    
12.     Next
    
13. End With
    
14. Sumstr = sum
    
15. End Function

复制代码

sdytsxd991122

wps

ba3259878266

Qs18 发表于 2024-9-3 07:51
试试.........

谢谢老师  很好用 效果好

ba3259878266

ykcbf1100 发表于 2024-9-3 08:09
参与一下，正则

老师 谢谢  很好用

ba3259878266

杨建营 发表于 2024-9-3 08:27
请楼主参考。

谢谢老师帮忙  实用

ba3259878266

Qs18 发表于 2024-9-3 07:51
试试.........

老师好  我放到其它表格 怎么不能用了  谢谢

ba3259878266

Qs18 发表于 2024-9-3 07:49
Function SumNumbersInString(inputString As String) As Double
    Dim regex As Object
    Set regex ...

老师你好  列改变了  这个怎样改变  我是小白 不懂  麻烦帮忙解决  谢谢

Function SumNumbersInString(inputString As String) As Double
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
   
    ' 正则表达式模式，匹配所有数字
    regex.Pattern = "\d+(\.\d+)?" ' 匹配整数和小数
    regex.Global = True ' 匹配所有出现的数字
   
    Dim matches As Object
    Set matches = regex.Execute(inputString)
   
    Dim total As Double
    total = 0
   
    ' 遍历所有匹配的数字并累加
    Dim match As Object
    For Each match In matches
        total = total + CDbl(match.Value)
    Next match
   
    ' 返回总和
    SumNumbersInString = total
   
    ' 清理
    Set regex = Nothing
    Set matches = Nothing
End Function

Sub qs()
    Dim testString As String
With Sheet1
arr = .Range("d2:d" & .Cells(Rows.Count, "d").End(3).Row)
For i = 1 To UBound(arr)
    If arr(i, 1) <> Empty Then
    testString = arr(i, 1)
    ' 调用函数并显示结果
    Dim result As Double
    result = SumNumbersInString(testString)
    arr(i, 1) = result
    End If
Next
.Range("g2").Resize(UBound(arr)) = arr
End With
End Sub

修改为  D2为F3   g2为M3

Qs18

ba3259878266 发表于 2024-9-3 11:22
老师你好  列改变了  这个怎样改变  我是小白 不懂  麻烦帮忙解决  谢谢

Function SumNumbersInString ...

可以的，给下面的过程就可以了

ba3259878266

Qs18 发表于 2024-9-3 15:10
可以的，给下面的过程就可以了

老师谢谢  好像不行  麻烦你看看附件  麻烦了       G3   H3   

Qs18

Sub 按钮1_Click()
Call qs
End Sub
Function SumNumbersInString(inputString As String) As Double
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
   
    ' 正则表达式模式，匹配所有数字
    regex.Pattern = "\d+(\.\d+)?" ' 匹配整数和小数
    regex.Global = True ' 匹配所有出现的数字
   
    Dim matches As Object
    Set matches = regex.Execute(inputString)
   
    Dim total As Double
    total = 0
   
    ' 遍历所有匹配的数字并累加
    Dim match As Object
    For Each match In matches
        total = total + CDbl(match.Value)
    Next match
   
    ' 返回总和
    SumNumbersInString = total
   
    ' 清理
    Set regex = Nothing
    Set matches = Nothing
End Function

Sub qs()
    Dim testString As String
With Sheet3
arr = .Range("g3:g" & .Cells(Rows.Count, "g").End(3).Row)
For i = 1 To UBound(arr)
    If arr(i, 1) <> Empty Then
    testString = arr(i, 1)
    ' 调用函数并显示结果
    Dim result As Double
    result = SumNumbersInString(testString)
    arr(i, 1) = result
    End If
Next
.Range("h3").Resize(UBound(arr)) = arr
End With
End Sub