Excel-VBA How do I stop the FIND method from looping back after the end of my sheet?

Question

I have a roughly formatted dataset to work with, and I'm using multiple Find methods to grab the datapoints I need. My sample database has three periods of data (all on one sheet), with multiple officer names. A name may or may not be in a particular period. I am using the variable officerSearch to store the address which serves as the anchor point for the following Find method. This works unless an officer name is not in a particular period, then my Find methods get off course. More detail in my code comments.

For Each k In dateArray.keys  'loops through my periods. Each key is a unique date
For i = 0 To officerListBox.ListCount - 1 
    If officerListBox.Selected(i) = True Then 'Performs _
          Find methods for each officer selected in list box
        officerSearch = Cells.Find(what:=CDate(k), LookIn:=xlFormulas, _
           searchorder:=xlByRows, searchdirection:=xlNext).Address 'Finds first instance of the first period (k)
        officerSearch = Cells.Find(what:=officerListBox.List(i), _
           after:=Range(officerSearch), LookIn:=xlValues, lookat:=xlWhole, _
           searchorder:=xlByRows, searchdirection:=xlNext).Address _
       'Finds officer name starting after the date cell was found. _
        But if the raw data set doesn't have the officer name in that period, _
        my Find method will find an officer name further on down the sheet _
        (or loop around at the beginning of the sheet) where the name is found in a different period. 
        officerSearch = Cells.Find(what:="Gross Loans", after:=Range(officerSearch), _
           LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
           searchdirection:=xlNext).Address 'Finds the cell labeled Gross Loans starting after an officer name is found
        officerSearch = Cells.Find(what:="Total 30 - 59 days past due", _ 
           after:=Range(officerSearch), LookIn:=xlValues, lookat:=xlWhole, _
           searchorder:=xlByRows, searchdirection:=xlNext).Address 'Finds the cell _
        labeled Total 30-59 days past due starting after _
        an officer name is found.

    End If
    Next i 'Starts the find loop over for the next selected officer name
Next k 'Starts the find loop over for the next period in the dataset

So my 1st, 3rd, and 4th Find methods are guaranteed to be present in the spot I expect, but the 2nd Find method may not be located in the period my loop is searching for, therefore throwing everything else off. I'm stumped on how to account for this.

Answer 1

Return the results of .Find as a Range object, and check for Nothing-ness, rather than chaining methods/properties (e.g., .Address) to an object that may not exist (i.e., the value is not found).

It's probably also a good idea to use different variable names for different things, while you can of course re-cast to officerSearch, it's potentially confusing and limiting to do so, and besides, variables are cheap.

Ideally, rather than starting with Cells.Find, you want to define a table or range of cells that you're concerned with, and then use that as the basis of .Find instead of invoking that method against the entire worksheet. This is the dataRange object you need to define.

Dim dataRange As Range '## range containing ALL data
Set data Range = Range(...) '## MODIFY AS NEEDED
Dim searchRange as Range, offName as Range, grossLoans as Range, pastDue as Range
'## Finds first instance of the first period (k) in the worksheet.Cells
Set searchRange = dataRange.Find(what:=CDate(k), LookIn:=xlFormulas, searchorder:=xlByRows, searchdirection:=xlNext) 

'## Define the range of cells containing this (k) period:
'    Assumes the (k) date is only present in one column
Set searchRange = searchRange.Resize(Application.WorksheetFunction.CountA(dataRange, CDate(k))

'## Finds officer name ONLY WITHIN THE searchRange, defined above
Set offName = searchRange.Find(what:=officerListBox.List(i), after:=Range(officerSearch.Cells(1).Address), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)  

'## Get out of here if the value isn't found
If offName Is Nothing Then
    ' you probably want a GoTo or Exit For statement here...
    MsgBox "Not found!"
Else
    '## Otherwise, find Gross Loans beneath Officer Name:
    Set grossLoans = searchRange.Find(what:="Gross Loans", after:=Range(offName.Address), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext) 
    Set pastDue = searchRange.Find(what:="Total 30 - 59 days past due", after:=Range(grossLoans.Address), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext) 
End If

How it Works:

1. First we define searchRange as the initial found range in the dataRange with your CDate(k) value.
2. Then we Resize this based on how many instances of that date value exist in the dataRange.
   • We'll use this resized searchRange as the parent of the .Find method for ALL subsequent calls, that way we are limiting it to the particular date period (k).
3. Then we find the officer name.
   • If the officer name isn't found, you probably want to do something else, right now there is a placeholder MsgBox.
4. If the officer name is found, then we find Gross Loans and Past Due ranges, assigning each to a corresponding range variable (grossLoans, pastDue respectively).

Note: Code is untested and may contain typos, misplaced parentheses, etc. In particular the searchRange.CurrentRegion is maybe not reliable, and it would be better if you can define the range of cells containing relevant data outside the loop.