郑州网站优化推广,江苏建设信息电子证书查询,南京开发,上海建网站公司排名系列文章目录 文章目录 系列文章目录前言一、按需求计数单元格数量1.需求 二、使用步骤1.vba源码2.整理后 总结 前言
一、按需求计数单元格数量
1.需求 一个表中有多个类型的单元格内容#xff0c;比如#xff1a;文字、数字、特殊字符、字母数字…… 我们要计数字母数字的…系列文章目录 文章目录 系列文章目录前言一、按需求计数单元格数量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 \dEnd With 遍历单元格For Each cell In rngIf regex.Test(cell.value) Then 如果单元格匹配正则表达式count count 1 增加计数Debug.Print 行 cell.Row ,列: cell.Column ,值: cell.valueSet matches regex1.Execute(cell.Value)For Each match In matchesoutputString outputString match.ValueDebug.Print outputStringNext matchDebug.Print cell.Row cell.ColumnDebug.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 myCollectionDebug.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 \dEnd With 遍历单元格For Each cell In rngIf regex.Test(cell.value) Then 如果单元格匹配正则表达式count count 1 增加计数Debug.Print 行 cell.Row ,列: cell.Column ,值: cell.valueSet matches regex1.Execute(cell.Value)For Each match In matchesoutputString outputString match.ValueDebug.Print outputStringNext matchDebug.Print cell.Row cell.ColumnDebug.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 myCollectionDebug.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总结
分享 读过的书是不是有很多不记得了但是它一直都是潜在的它在我们的出言有尺上嬉闹有度上做事有余上说话有德上
