Visual Basic 如何使用Findnext()

问题描述

我是一个Visual Basic的新手，也是一个会计师，所以如果这个问题太简单的话，我很抱歉。我似乎在处理查找和查找下一个的结构时很困惑。

我试图找到一个总账代码，并在其旁边放置另一个从查找表中获取的代码。例如，72001…我尝试让系统返回该范围，然后向左移动一个单元格，并将另一个单元格中的数字（比如240）放在左侧。最终结果应该是左侧单元格上的240（从另一单元格的表格中获取），右侧单元格上的72001。

我试图通过在一个子程序中创建较小的代码块来简化问题，但仍然难以理解甚至是基本级别的find/findnext的概念。目前为止，我只是在测试这个函数，但它仍然没有按照我的意愿工作；我不明白为什么会这样。

Sub abc()
Dim A As Range
Do While Not A Is Nothing
Set A = Range("B1:B100").Find(what:="A")
MsgBox (A)
Loop
End Sub

当A不是一个空数据集时，代码不应该保持循环吗？我在B列上放了一堆”A”，但是我仍然没有收到任何消息框。为什么没有任何事情发生？我甚至尝试将”not”去掉，但仍然没有发生任何事情。我不明白。

我尝试了上面的代码都无效，还尝试了下面的代码也遇到令人沮丧的”无效使用对象错误”。我只是想用查找函数替换A字符。

Sub abc()
Dim A As Range
Dim B As Range


Set A = Range("B1:B100").Find(what:="A", after:="A1")
If A Is Not Nothing Then A.Value = Z
Else
Do Until Not B Is Nothing
Set B = Range("B:B100").Find(what:="A", after:="A1")
MsgBox ("A")
Loop
End Sub

解决方案

Find/FindNext 基础知识

当你写下Dim A As Range时，实际上意味着Dim A As Range: Set A = Nothing。因此，当你继续执行Do While Not A Is Nothing (Do While A Is something)时，显然不会进入循环(因为A什么都不是)。
尝试并测试以下代码。
此外，详细研究文档，因为Find方法比我在这里分享的要多得多。

Sub abc()

    Const SearchString As String = "A"

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("B1:B100")

    ' The following will attempt to find a cell equal to "A".
    ' It will start looking in cell `B1`, after the supplied last cell 'B100'
    ' since the default parameter of 'SearchDirection' is 'xlNext'.
    ' It will find "a" and "A" since the default parameter of 'MatchCase'
    ' is 'False'.
    Dim cell As Range: Set cell = rg.Find(What:=SearchString, _
        After:=rg.Cells(rg.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole)

    Dim FirstAddress As String

    If Not cell Is Nothing Then
        ' Store the address of the first found cell in a variable.
        FirstAddress = cell.Address
        Do
            ' It is advisable when developing a loop, to use
            ' 'DoEvents' so you can exit an endless loop with 'Esc'
            ' or 'Pause/Break'. When done, out-comment it.
            'DoEvents
            ' Do something with the found cell.
            MsgBox "Found """ & cell.Value & """ in cell """ _
                & cell.Address(0, 0) & """.", vbInformation
            ' Find the next cell equal to "A". Note how you are supplying
            ' the current cell as the parameter for the 'After' argument
            ' so it will start by looking into the next cell.
            Set cell = rg.FindNext(After:=cell)
        ' Since 'Find' or 'FindNext' will try to find 'forever',
        ' from 'B1' to 'B100', and again, and again, you need to compare
        ' the address of the newly found cell with the address of the first
        ' found cell to be able to exit the loop once it got back
        ' to the first found cell.
        Loop While cell.Address <> FirstAddress
        'Loop Until cell.Address = FirstAddress
    Else
        MsgBox "No """ & SearchString & """ was found.", vbExclamation
    End If

End Sub