excel - Excel VBA-必需的对象

我不必暂时使用Excel VBA，但我决定对其进行深入研究，以构建一些用户友好和可访问的模板来跟踪月度出货量。

这个特定的搜索功能让我头疼，我怀疑这是因为我对语法非常不了解。

至此，我在调用用户表单时收到“对象必需”错误，但从逐步了解到，我已经明确声明并定义了与问题行相关的所有内容。

感谢任何帮助/复习者。我的脸热切地等待着我的手掌。

编辑：当前工作代码发布在下面的响应中

完整代码

Private Sub UserForm_Initialize()
'Clear result message
Me.rResult.caption = ""

'BOL (cached)
Dim BOL As Range
Set BOL = ThisWorkbook.Sheets("Values").Range("$A$3")
'Addresses (cached)
Dim addr As Range
Set addr = ThisWorkbook.Sheets("Values").Range("$A$4:$D$21")
Dim i As Range
'Search Range
Dim srange As Range
'Target Range
Dim trange As Range
'First result
Dim fr As Range
'Result counter
Dim c As Integer
c = 0

With ThisWorkbook.ActiveSheet
    'Set search range
    Set srange = .Range(.Cells(7, 6), _
    .Cells(.Cells(Rows.Count, 5).End(xlUp).Row, _
    .Cells(6, Columns.Count).End(xlToLeft).Column))

    'Find search results in search range
    Set trange = srange.Find(BOL.Value, LookIn:=xlValues)
    'If the cell is not empty and the header in row 6 has a value of "BOL"
    If Not trange.Value Is Nothing Then '***Problem Line***
        If .Cells(6, trange.Value).Value = "BOL" Then
        'set first address
        Set fr = .Range(trange.Address)
        Do
            'result counter
            c = c + 1
            'save each address within cache range
            For Each i In addr
                If i.Value = "" Then i.Value = trange.Address
            Next i
            Set trange = trange.FindNext(trange)
        Loop While Not trange Is Nothing And trange.Address <> fr.Address
        End If
    End If
End With

'Select the first address
If c <> 0 Then fr.Select

'Result message
Me.rResult.caption = "Search found " & c & _
    " result(s) for BOL: " & BOL.Value & "."

'clear cached BOL
BOL.Clear

End Sub

最佳答案

我认为这是trange结束时的.value。当您进行查找但未找到任何内容时，则trange会返回为空。然后，您要求的是空值，这将给您对象所需的错误。

只需将其更改为If Not trange Is Nothing...，但我认为@Abe Gold是正确的，您将需要两个if语句。因为如果trange为空，则无法检查'.cells（6，trange.value）.value =“ BOM”`。

您的if语句应如下所示（取自上面的@Abe Gold代码）

If Not trange Is Nothing Then
    If .Cells(6, trange.Value).Value = "BOL" Then
      'set first address
      Set fr = .Range(trange.Address)
      Do
        'result counter
         c = c + 1
        'save each address within cache range
        For Each i In addr
            If i.Value = "" Then i.Value = trange.Address
        Next i
        Set trange = trange.FindNext(trange)
     Loop While trange.Address <> fr.Address
   End IF
End If