欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 教育 > 高考 > vba学习系列(9)--按需求计数单元格数量

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

2025/5/2 4:22:36 来源:https://blog.csdn.net/qq_45365214/article/details/127634459  浏览:    关键词:vba学习系列(9)--按需求计数单元格数量

系列文章目录

文章目录

  • 系列文章目录
  • 前言
  • 一、按需求计数单元格数量
    • 1.需求
  • 二、使用步骤
    • 1.vba源码
    • 2.整理后
  • 总结


前言

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

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

总结

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

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

热搜词