|
一列中有纯数值,也有包含单位的数值,如:100,23,23m,45m,234m,
用代码计算所有数值的和
用代码计算仅包含单位的和
谢谢!
- Sub demo()
- Dim arr
- Dim lSum1&, lsum2&, i&
- arr = Range("a1").CurrentRegion
- For i = 1 To UBound(arr)
- If IsNumeric(arr(i, 1)) Then lSum1 = lSum1 + arr(i, 1)
- If Not IsNumeric(arr(i, 1)) Then lsum2 = lsum2 + Val(arr(i, 1))
- Next
- MsgBox lSum1 & vbCr & lsum2
- End Sub
- Sub demo2()
- On Error Resume Next
- Dim rg As Range, lsum2&
- MsgBox Application.WorksheetFunction.Sum(Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers))
- For Each rg In Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlTextValues)
- lsum2 = lsum2 + Val(rg.Value)
- Next
- MsgBox lsum2
- End Sub
复制代码
|
|