在合并的单元格中查找字符串的方法 [英] a way to find a string within merged cells
问题描述
我需要在目录中的文件中搜索字符串的出现并返回计数.
I need to search through files within a directory for the occurrences of a string and return a count.
为了进行测试,我将4个工作簿(每个工作簿各有5个工作表)放入C:\ test目录.我正在寻找工作簿中任何地方出现的氨气单词的计数.即使我确定它存在,我正在使用的代码也将其重新设置为"0".我相信这是因为lookin不适用于合并的单元格.有什么技巧可以完成这项工作吗?
For testing I have put 4 workbooks with 5 worksheets each into C:\test directory. I am looking for a count of the occurrences of the word ammonia anywhere within the workbooks. The code I am using is retuning "0" even though I am certain it exists. I believe its because lookin does not work with merged cells. Are there any tricks for making this work?
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
代码未在合并的单元格中找到该值.
Code is not finding the value in a merged cell.
推荐答案
欢迎使用SO.
您提供的代码接近应有的代码.但是,它将在每个工作表中仅发现一个事件.我不确定这是否是设计使然,所以在下面的代码中,我演示了如何查找每个工作表中的所有事件.
The code you have provided is close to what it should be. However, it will only find one occurrence per worksheet. I am not sure if that's by design, so in the following code I'm demonstrating how you can find all the occurrences per worksheet.
此外,我不确定我了解如何在每个循环中增加 count
的逻辑.当前,您的代码找到了值为氨"的单元格,然后向右移3个单元格,并将其中的任何值添加到 count
中.同样,我不确定这是否是设计使然.
Also, I am not sure I understand the logic of how you increase the count
in each loop. Currently your code finds the cell whose value is "Ammonia" and then goes 3 cells to the right and adds whatever value is there to count
. Again I am not sure if that's by design.
此外,您不需要 sh.Activate
和 found.Select
.
下面是我建议的代码以及解释其工作原理的注释.
Below is the code I would suggest, along with comments that explain how it works.
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
这篇关于在合并的单元格中查找字符串的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!