时间戳

首页 > 解决方案 > 下标超出范围 - 使用 Excel VBA 查找下一个

excel - 下标超出范围 - 使用 Excel VBA 查找下一个

问题描述

我在此行收到“下标超出范围”错误消​​息：

Set NextRow = Sheets(xSheet).Cells.FindNext(After:=bookmark)

理论上，我在设置 foundCell 的那一刻就设置了书签。

我想：

  1. 搜索与“Str”变量中的文本匹配的第一条记录。

  2. 使用 Excel 的查找功能，查找文本及其所在的行并根据单元格值填充用户表单

  3. 计算总结果，以便我可以在用户表单的某处找到 [1] of [3]

  4. 使用按钮向后（上一个和下一个）我的搜索结果。

'Global Variables
Dim foundCell
Dim bookmark


Private Sub btnSearch_Click()

Dim Str
Dim FirstAddr
Dim xSheet

xSheet = "Office Spaces"

Str = "B-32"

    With Sheets(xSheet)

        Set foundCell = .Cells.Find(What:=Str, After:=.Cells(1, 1), _
                        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        Set bookmark = foundCell

    End With


    If Not foundCell Is Nothing Then

        MsgBox ("""Bingo"" found in row " & foundCell.Row)

        UserForm1.location.Text = Sheets(xSheet).Cells(foundCell.Row, 3).Value
        UserForm1.office.Value = Sheets(xSheet).Cells(foundCell.Row, 2).Value
        UserForm1.floor.Value = Sheets(xSheet).Cells(foundCell.Row, 1).Value
        UserForm1.status.Value = Sheets(xSheet).Cells(foundCell.Row, 4).Value
        UserForm1.telephone.Value = Sheets(xSheet).Cells(foundCell.Row, 5).Value
        UserForm1.mobile.Value = Sheets(xSheet).Cells(foundCell.Row, 6).Value
        UserForm1.owner.Value = Sheets(xSheet).Cells(foundCell.Row, 7).Value
        UserForm1.notes.Value = Sheets(xSheet).Cells(foundCell.Row, 8).Value
        UserForm1.recnum.Value = 1
        FirstAddr = foundCell.Address

        Dim i

        Do Until foundCell Is Nothing

            Set foundCell = Sheets(xSheet).Cells.FindNext(After:=foundCell)

            i = i + 1

            If foundCell.Address = FirstAddr Then Exit Do
        Loop

        If i > 1 Then
            btnPrev.Enabled = True
            btnNext.Enabled = True
        End If

        UserForm1.recmax.Value = i

    Else
        MsgBox ("Bingo not found")
    End If

End Sub


Private Sub btnNext_Click()

Dim NextRow

Set NextRow = Sheets(xSheet).Cells.FindNext(After:=bookmark)

UserForm1.location.Value = Sheets(xSheet).Cells(NextRow.Row, 3).Value
UserForm1.office.Value = Sheets(xSheet).Cells(NextRow.Row, 2).Value

End Sub

标签： excelvba

解决方案

该错误是由于xSheet未分配适当的值引起的。为什么它没有价值？因为它是在一个 Sub 中定义和分配的，并在另一个 Sub 中使用。解决此问题的一种方法是xSheet像您为bookmark.

最好也考虑其他评论中的建议。这些建议将改进您的代码。

推荐阅读