☰

VBA Excel：Findnext 循环并返回不包含搜索字符串的单元格

tag-icon • tag-icon microsoft-excel vba find
VBA Excel：Findnext 循环并返回不包含搜索字符串的单元格

代码会找到包含“Balance”的单元格，并将该单元格保存到变量中，foundcell以便在电子表格中找到余额列。有多个余额列。我只用 测试了这一点，FIND它通过在 D8 中找到第一个包含“Balance”的列来工作。

一旦我为 添加了循环findnext，它就会返回 D8、G8、H8、I8 和 A9，然后崩溃，因为 A9 是错误的。FINDNEXT应该只返回 I8 并停止，因为搜索范围内有 2 个单元格包含单词“balance”。G8、H8 和 A9 不包含单词“balance”。

    Sub ReFill_Credit()
' Macro - ReFill formula columns for Credit sheet
'https://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba

Dim StartRow As Integer
Dim FindString As String
Dim foundcell As Range
Dim LastRow As Integer
Dim LastRow1 As Integer
Dim CopyCell As Range
Dim endcell As Range
Dim firstaddress As String
Dim SrchRng As Range
Dim Lastcell As Range
Dim coloffset As Integer
Dim cellofffset As Range
Dim colbal As Integer

With Sheets("Credit")

'citi = 4 '"d"
'chase = 9 '"i"
'colbal = citi
FindString = "Balance"

Set SrchRng = .Range("a1:k15")
Set Lastcell = SrchRng.Cells(SrchRng.Cells.Count)

'Find the Balance cell
Set foundcell = SrchRng.Find(What:=FindString, _
    LookAt:=xlWhole, _
    LookIn:=xlValues, _
    SearchOrder:=xlByRows, _
    MatchCase:=False)
Debug.Print ""

'Test to see if anything was found
If Not foundcell Is Nothing Then
    firstaddress = foundcell.Address
 'end If
 Debug.Print "firstaddress = " & firstaddress

'Loop to next "balance" column ***************************************************
Do
    Debug.Print ""
    Debug.Print "**********starting 'do until'*********"
    Debug.Print "foundcell (1st search) is: " & foundcell.Address

'Test if cycled thru back to the first cell
    StartRow = foundcell.Row + 1
    Debug.Print "startrow is  " & StartRow
    colbal = foundcell.Column
    FirstCell = Cells(StartRow, colbal)

'Finds the last non-blank cell on a sheet/range.
    coloffset = colbal - 1
    'Debug.Print "colbal is:  " & colbal
    Debug.Print "coloffset is:  " & coloffset

    LastRow = .Columns(coloffset).Find(What:="*", _
                 LookAt:=xlPart, _
                 LookIn:=xlFormulas, _
                 SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious, _
                 MatchCase:=False).Row
    Debug.Print "last row is  " & LastRow
    LastRow1 = LastRow + 1

'Fill formula down COL D
    Set CopyCell = .Cells(StartRow, colbal)
    Set endcell = .Cells(LastRow1, colbal)
    Debug.Print "copycell = " & CopyCell
    Debug.Print "copycell.add = " & CopyCell.Address
    Debug.Print "endcell is  " & endcell.Address
'=IF(ISTEXT(D8),D$7,D8)+C9 / starting at d9
    CopyCell.Select
    Selection.AutoFill Destination:=.Range(CopyCell, endcell),     Type:=xlFillValues
    Debug.Print "findstring is " & FindString

'Find next *********************************************************
    Set foundcell = SrchRng.FindNext(foundcell)
 '       If foundcell.Address = firstfound Then Exit Do
Loop While Not foundcell Is Nothing And foundcell.Address <> firstaddress

End If
End With
    End Sub

答案1

我使用以下命令交换了计算 LastRow 的块FIND：

    LastRow = .Columns(coloffset).Find(What:="*", _
             LookAt:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Row

改为：

LastRow = .Cells(Rows.Count, coloffset).End(xlUp).Row

希望此方法与之前的方法相同，并且没有出现不可预见的问题。

相关内容