在一系列单元格中进行宏搜索 [英] Macro search in a range of cells

问题描述

想要编写一个宏，可以搜索单元格范围（或单元格之后）中的特定单词，我们来说就是说你好。假设我的电子表格如下所示：

Want to write a Macro that would search for a particular word in a range of cells (or after a cell), let's say "hello" in our case. Suppose my spreadsheet looks like this:

您好，你好，

Hello Nancy你好，
你好，数量2

Hello user
Hello Nancy
Hello count 2

电子表格的内容每天都会更改，因此我可能会有不同的Hello每天。我想将最后一个Hello旁边的数字（2）复制到另一个单元格。如果hello的字数没有退出，它将在该单元格中加0（请注意，即使字数为0，在该电子表格中可能仍然有你好）。最后一个Hello的位置将始终在单元格A17之后。

The content of the spread sheet change daily, so that I may have different number of 'Hello's everyday. I want to copy the number (2) beside the last 'Hello' to another cell. If the word count of hello doesn't exits, it will put 0 in that cell(Note that even if the word count is 0, there might still be 'hello' in this spread sheet). The location of the last Hello will always be after Cell A17.

我正在考虑将参数设置为单元格A17，并将SearchOrder更改为xlByColumns，但是当搜索到达搜索范围的末尾，它包围到范围的开头。

I was thinking about setting the parameter After to cell A17, and change SearchOrder to xlByColumns, but When the search reaches the end of the search range, it wraps around to the beginning of the range.

发生这种环绕时应该如何停止搜索？

How should I stop the search when this wraparound occurs?

我还尝试使用Find方法在A17到B22范围内搜索：

I also try to use the Find method within With to search within range A17 to B22:

With Sheets("Data").Range("A17:B22")
    Set hello = Cells.Find(What:="hello", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
End With
If Not hello Is Nothing Then
    With Sheets("Data").Range("A17:B22")
        Cells.Find(What:="Hello", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, 1).Range("A1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("B17").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
Else
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "0"
End If

但是它仍然会将搜索定位到第一个Hello '在电子表格中。

But it will still locate the search to the first 'Hello' in the spreadsheet.

推荐答案

尝试这个：

Function GetLastNumber(TheRange As Range, TheWord As String) As Variant
Dim Finder
'You can add LookAt:=xlWhole if you want entire word only
Set Finder = TheRange.Find(TheWord, SearchDirection:=xlPrevious)
If Finder Is Nothing Then
    GetLastNumber = CVErr(xlErrNA)
Else
    'Return the value of the cell one column to the right of our search word
    GetLastNumber = Finder.Offset(0, 1).Value
End If
End Function

您可以这样使用：

Sub Test()
Range("D1") = GetLastNumber(Range("A1:A11"), "Hello")
End Sub

您还可以将其用作公式： p>

You can also use it as a formula:

=GetLastNumber(A1:A11,"Hello")

结果：

这篇关于在一系列单元格中进行宏搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！