Excel VBA - Object Required

Question

I haven't had to use Excel VBA in awhile, but I decided to dig it up to build a few user-friendly and accessible templates for tracking monthly shipments.

This specific search function is giving me a headache and I suspect it's because I'm quite rusty with the syntax.

I've got myself to this point where I'm receiving an 'object required' error when calling a user form but from what I can see from stepping through, I've explicitly declared and defined everything related to the problem line.

Appreciate any help/refreshers. My face eagerly awaits my palm.

Edit: Current working code posted in response below

Full code

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

Answer 1

This If statement was only being applied to the first found address. I moved it into the loop to be applied to every iterative result.

If .Cells(6, trange.Column).Value = "BOL" Then

Added a number of other changes/fixes outlined in above responses. Thanks!

Current working code snip:

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

Answer 2

I know there is both an accepted answer and two most upvoted ones, but I'm both proposing improvements and mainly missing something (see last point)

please consider this code:

'Find search results in search range
Set trange = srange.Find(BOL.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) '<~~always specify those parameters since they are saved from any Find() usage, even that from UI
If Not trange Is Nothing Then 'If the cell is not empty and the header in row 6 has a value of "BOL"
    If .Cells(6, trange.Column).Value = "BOL" Then
        Set fr = trange '<~~store first trange occurrence found
        Do
            c = c + 1 'update result counter
            addr.SpecialCells(xlCellTypeConstants) = trange.Address '<~~save current address within cache range
            Set trange = srange.FindNext(trange) '<~~search next occurrence of trange in srange
        Loop While Not trange.Address <> fr.Address
    End If
End If

whose differences from the OP's one are hereby summarized:

• Set trange = srange.Find(BOL.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

  always specify those parameters since they are saved from any Find() usage, even that from UI, so that yo may find yourself involuntarily looking at string Part and/or minding Case Matching

• If Not trange Is Nothing Then

  ok, that has already been established by all answers and comments

• Set fr = trange

  since .Range(trange.Address) is redundant

• addr.SpecialCells(xlCellTypeConstants) = trange.Address

  instead of the For Each i In addr loop

• Set trange = srange.FindNext(trange)

  here there was Set trange = trange FindNext(trange)

  shouldn't the second trange be srange?

Answer 3

Another change.

If Not trange.Value Is Nothing Then

should be

If Not trange Is Nothing Then

Rewrite your IF into two IFs:

 If Not trange.Value 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

Answer 4

I think it's your .value at the end of trange. When you do a find and nothing is found then the trange comes back as nothing. Then you are asking for the value of nothing, which is giving you the object required error.

Just change it to If Not trange Is Nothing... but I think @Abe Gold is right, you'll need two if statements. Because you can't check '.cells(6, trange.value).value = "BOM"` if trange is nothing.

Should look like this for your if statement (Taken from @Abe Gold code above)

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