文字数字的颜色单元格求和

AA5242

新年好！各位老师帮我看看，，，文字数字的颜色单元格求和

一把小刀闯天下

Option Explicit

Sub test()
  Dim rng, mark, sum, i, j, t, flag
  mark = "-0.123456789"
  flag = Split("工资,飞姐,取,借,公司,转,快递费,搞,购买,帆布,二姐,仓库", ",")
  For Each rng In [a3:c12]
    t = Trim(rng.Value)
    If Len(t) Then
      For j = 0 To UBound(flag)
        If InStr(t, flag(j)) Then Exit For
      Next
      If j < UBound(flag) + 1 Then
        If IsNumeric(Right(t, 1)) Then
          For j = Len(t) To 2 Step -1
            If InStr(mark, Mid(t, j - 1, 1)) > 0 Xor InStr(mark, Mid(t, j, 1)) > 0 Then
              sum = sum + Val(Mid(t, j))
              Exit For
            End If
          Next
        End If
      End If
    End If
  Next
  [g3] = sum
End Sub

AA5242

一把小刀闯天下 发表于 2019-2-11 22:48
Option Explicit

Sub test()

非常好用，，多谢啦！

kzg2013

Sub SUMCondition()
Dim FCOLOR&, vl&
Dim REG As Object

FCOLOR = Cells(6, 2).DisplayFormat.Font.Color
CL = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Set REG = CreateObject("VBSCRIPT.REGEXP")
With REG
.Pattern = "\-*\d+"
.Global = True
End With

For Each cel In Range("A3:C" & CL)

   If cel.DisplayFormat.Font.Color = FCOLOR Then
       Set tx = REG.Execute(cel)
        If tx.Count >= 1 Then
        For k = 0 To tx.Count - 1
        vl = vl + Val(tx.Item(k))
        Next k
        End If
    End If
Next

Cells(4, "g") = vl

Set REG = Nothing
End Sub