vba学习系列(9)--按需求计数单元格数量

系列文章目录

---

前言

一、按需求计数单元格数量

1.需求

一个表中有多个类型的单元格内容，比如：文字、数字、特殊字符、字母+数字……
我们要计数字母+数字的单元格数量
同时提取字母+数字单元格的数字部分，判断数字部分是否相同，然后计数不同的单元格数量

二、使用步骤

1.vba源码

代码如下（示例）：

Sub CountSpecificFormatStrings()Dim rng As RangeDim cell As RangeDim count, countkey As IntegerDim regex, regex1 As ObjectDim cellAddress As StringDim numbers As StringDim matches As ObjectDim match As VariantDim cellNumber As StringDim cellValue As StringDim myCollection As CollectionSet myCollection = New CollectionDim value As VariantDim lastValue As VariantDim uniqueCount As IntegerDim ws As WorksheetSet ws = ActiveSheetDim cellValueCollection As CollectionDim i, j, k As IntegerDim valueToAdd As StringDim key As VariantDim dict As ObjectDim myArray As VariantDim item, ele As VariantDim elements As VariantDim uniquekeys As New CollectionuniqueCount = 0lastValue = ""Set cellCollection = New CollectionSet dict = CreateObject("Scripting.Dictionary")' 设置要检查的范围Set rng = ws.Range("A3:DR200") ' 假设我们在Sheet1的A1:A10范围内查找count = 0' 创建正则表达式对象Set regex = CreateObject("VBScript.RegExp")' 设置正则表达式模式：一个字母后面跟随任意数量的数字' 这里的模式是 [A-Za-z][0-9]+，其中 [A-Za-z] 匹配任意一个字母，[0-9]+ 匹配一个或多个数字With regex.Global = True.IgnoreCase = True.pattern = "[A-Za-z][0-9]+"End With' 创建正则表达式对象Set regex1 = CreateObject("VBScript.RegExp")' 设置正则表达式模式：一个字母后面跟随任意数量的数字' 这里的模式是 [A-Za-z][0-9]+，其中 [A-Za-z] 匹配任意一个字母，[0-9]+ 匹配一个或多个数字With regex1.Global = True.IgnoreCase = True.pattern = "\d+"End With' 遍历单元格For Each cell In rngIf regex.Test(cell.value) Then ' 如果单元格匹配正则表达式count = count + 1 ' 增加计数Debug.Print "行：" & cell.Row & ",列:" & cell.Column & ",值:" & cell.value'Set matches = regex1.Execute(cell.Value)'For Each match In matches'outputString = outputString & match.Value'Debug.Print outputString'Next match'Debug.Print cell.Row & cell.Column'Debug.Print Cells(cell.Row, cell.Column).ValueSet matches = regex1.Execute(Cells(cell.Row, cell.Column).value)For Each match In matchesmyCollection.Add matchNext matchEnd IfNext cellFor Each item In myCollection'Debug.Print itemNextcountkey = myCollection.countFor i = myCollection.count To 1 Step -1For j = 1 To i - 1If myCollection(i) = myCollection(j) ThenmyCollection.Remove (i)countkey = countkey - 1Exit ForEnd IfNext jNext iFor Each ele In myCollectionDebug.Print eleNext elews.Cells(2, 69).value = countkey' 显示计数结果MsgBox "有 " & countkey & " 个单元格符合指定格式。"
End Sub

2.整理后

代码如下（示例）：

Sub CountSpecificFormatStrings()Dim rng As RangeDim cell As RangeDim count, countkey As IntegerDim regex As ObjectDim matches As ObjectDim match As VariantDim myCollection As CollectionSet myCollection = New CollectionDim ws As WorksheetSet ws = ActiveSheetDim i, j As IntegerDim key As VariantDim item, ele As Variant' 设置要检查的范围Set rng = ws.Range("A3:DR200") ' 假设我们在Sheet1的A1:A10范围内查找count = 0' 创建正则表达式对象Set regex = CreateObject("VBScript.RegExp")' 设置正则表达式模式：一个字母后面跟随任意数量的数字' 这里的模式是 [A-Za-z][0-9]+，其中 [A-Za-z] 匹配任意一个字母，[0-9]+ 匹配一个或多个数字With regex.Global = True.IgnoreCase = True.pattern = "[A-Za-z][0-9]+"End With' 创建正则表达式对象Set regex1 = CreateObject("VBScript.RegExp")' 设置正则表达式模式：一个字母后面跟随任意数量的数字' 这里的模式是 [A-Za-z][0-9]+，其中 [A-Za-z] 匹配任意一个字母，[0-9]+ 匹配一个或多个数字With regex1.Global = True.IgnoreCase = True.pattern = "\d+"End With' 遍历单元格For Each cell In rngIf regex.Test(cell.value) Then ' 如果单元格匹配正则表达式count = count + 1 ' 增加计数Debug.Print "行：" & cell.Row & ",列:" & cell.Column & ",值:" & cell.value'Set matches = regex1.Execute(cell.Value)'For Each match In matches'outputString = outputString & match.Value'Debug.Print outputString'Next match'Debug.Print cell.Row & cell.Column'Debug.Print Cells(cell.Row, cell.Column).ValueSet matches = regex1.Execute(Cells(cell.Row, cell.Column).value)For Each match In matchesmyCollection.Add matchNext matchEnd IfNext cellFor Each item In myCollection'Debug.Print itemNextcountkey = myCollection.countFor i = myCollection.count To 1 Step -1For j = 1 To i - 1If myCollection(i) = myCollection(j) ThenmyCollection.Remove (i)countkey = countkey - 1Exit ForEnd IfNext jNext iFor Each ele In myCollectionDebug.Print eleNext elews.Cells(2, 69).value = countkey' 显示计数结果MsgBox "有 " & countkey & " 个单元格符合指定格式。"
End Sub

---

总结

分享：
读过的书是不是有很多不记得了，但是它一直都是潜在的，它在我们的出言有尺上，嬉闹有度上，做事有余上，说话有德上