excel - 一种在合并单元格中查找字符串的方法
问题描述
我需要在目录中的文件中搜索字符串的出现并返回计数。
为了测试,我将 4 个工作簿和 5 个工作表放入 C:\test 目录。我正在寻找工作簿中任何地方出现氨这个词的计数。我使用的代码正在重新调整“0”,即使我确定它存在。我相信它是因为lookin 不适用于合并的单元格。有什么技巧可以使这项工作吗?
Sub LoopThroughFiles()
Range("'sheet1'!A6:M10000").ClearContents
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
directory = "C:\Test\"
fileName = Dir(directory & "*.xl??")
i = 5
Do While fileName <> ""
i = i + 1
If fileName <> "" Then
Dim wbk As Workbook
With wbk
Set wbk = Workbooks.Open(directory & fileName)
End With
Dim sh As Worksheet
Dim found As Range
Dim count As Integer
For Each sh In wbk.Worksheets
Set found = sh.Cells.Find(what:="Ammonia", LookIn:=xlFormulas)
If Not found Is Nothing Then
sh.Activate
found.Select
count = count + sh.Range(found.Address).Offset(0, 3).Value
Else
End If
Next sh
wbk.Close
End If
fileName = Dir()
Loop
Range("'Sheet1'!C2").Value = count
End Sub
代码未在合并单元格中找到值。
解决方案
欢迎来到 SO。
您提供的代码接近应有的代码。但是,它只会在每个工作表中找到一次。我不确定这是否是设计使然,因此在下面的代码中,我将演示如何找到每个工作表的所有匹配项。
另外,我不确定我是否理解如何增加count
每个循环中的逻辑。目前,您的代码找到值为“氨”的单元格,然后向右移动 3 个单元格并将任何值添加到count
. 同样,我不确定这是否是设计使然。
此外,您不需要 of sh.Activate
和found.Select
。
下面是我建议的代码,以及解释其工作原理的注释。
Option Explicit
Sub LoopThroughFiles()
'
' your code to loop through workbooks
'
Debug.Print numOfOccurrences("Ammonia", wbk) 'call the search function and print the number of occurrences to the immediate window
'
' your code continues
'
End Sub
Public Function numOfOccurrences(keyword As String, wb As Workbook) As Long
Dim sht As Worksheet
Dim found As Range
Dim count As Long
Dim firstOccurence As String
count = 0
For Each sht In wb.Worksheets 'loop through sheets
Set found = sht.Cells.Find(what:=keyword) 'search for the first occurrence if any
If Not found Is Nothing Then 'if the keyword is found once, then we need to search for more occurrences
firstOccurence = found.Address 'store the address of the first occurence
Do
Set found = sht.Cells.FindNext(found) 'search for the next occurrence in the same sheet
count = count + 1 'keep track of the number of occurences
If found Is Nothing Then
GoTo DoneFinding 'this deals with what seems to be a bug when using .FindNext with merged cells
End If
Loop Until found.Address = firstOccurence 'repeat until the search goes full circle back to the first occurrence
End If
DoneFinding:
Next sht
numOfOccurrences = count
End Function
推荐阅读
- r - 将数据分成十分位数并计算每个十分位数的平均值
- java - 使用 while()+sleep() 代替信号量或互斥量?
- ios - 通过目标 c 初始化 swift nsobject
- jquery - 用户仅在具有特定类名的 div 之前/之后附加
- java - 如何实现 Comparable 接口?
- android - RecyclerView 行中未显示效果
- hadoop - 共享外部配置单元元存储如何依赖特定版本的 HDInsight(Hadoop) 群集?
- php - 如果复选框被禁用,则更改文本颜色
- jquery - $(...).datepicker 不是 Rails 6 中的函数
- javascript - 减少 Typescript/JS 中嵌套对象数组的级别