Error 9 Subscript out of Range tried everything

Question

Receiving an Run-time error 9 subscript out of range on the Set finder = .Find(clientName, LookIn:=xlValue, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext) line and i've tried a bunch of things to fix it. I'm missing something vital here and i'm not sure what it is. Read through all the other posts on here and none seem to help/correlate too closely.

Things to Note: 1) variable clientName is a standard string that is correctly valued when passed into the function. 2) I tested the worksheet object by adding contactsMaster.Activate line prior to the find line and it activates to the sheet correctly, which leads me to believe it has nothing to do with the name of the sheet or workbook (I use getBook = Activeworkbook.Name in the main sub in order to avoid issues with name changes from the user. 3) I've changed the range to contactsMaster.Range("A:C") and that didn't change anything either. 4) The data is stored in columns A through C. One client name per cell. This function is intended to standardize the naming convention method by client name so that I can find the files from any macro using the same convention. 5) Initially I thought using .Address on the error line was throwing an error for some reason, but it doesn't appear to be the case as I have removed it and still receive the same error. Code Below:

Function GetClientName(clientName As String) As String

'Gets specific client name to save excel file in a standardized format to be found easily

    'Sets Objects/vars
    Dim finder As Range
    Dim location As Long
    Dim contactsMaster As Worksheet

    Set contactsMaster = Workbooks("EEB MACRO DEVELOPMENT BOOK").Sheets("Contacts Master")

    With contactsMaster.Range("A1:C1000")
       Set finder = .Find(clientName, LookIn:=xlValue, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    End With

    'Prevents error for not finding client in contacts master
    If finder Is Nothing Then
        GoTo endFunc
    End If

    location = finder.Row

    GetClientName = contactsMaster.Cells(location, 1)

    Exit Function

endFunc:

    GetClientName = clientName
End Function

BruceWayne BruceWayne · Accepted Answer · 2016-01-08T15:07:53

You just need to make it LookIn:=xlValues, note the "S" at the end. Also, you're using Lookin:=, LookAt:=, so just for continuity, I suggest putting What:= too at the start, so you get:

Set finder = .Find(What:=clientName, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

Error 9 Subscript out of Range tried everything

1 Answers