Problem handling a run-time 91

[JayStax]

'/////////////////////////////Variable Declaration//////////////////////////////////////////////////

Dim masterData As Worksheet, console As Worksheet, inputCell As Range, firstNames As Range, lastName As Range, countWhichName As Range 'EZ refs for sheets/ranges


Dim client As String, searchInput As String 'text variable for client and for search value

Dim matchCountLast As Integer

Dim matchCountFirst As Integer

Dim inputBoxValue As Variant



'/////////////////////////////////////Constants set//////////////////////////////////////////////////

Set masterData = Sheets("Master Data")

Set console = Sheets("Client Console")

Set firstName = masterData.Range("C2:C99999")

Set lastName = masterData.Range("B2:B99999")

Set inputCell = console.Range("E3") 'setting variable equal to cell that pulls name

'///////////////////////////////////// For USERFORMS //////////////////////////////////////////////////////////////


Dim recordType As String, firstRecord As String, lastRecord As String


'////////////////////////////// Section for finding and pulling client info ///////////////////////////////////////


searchInput = inputCell.Value


client = lastName.Find(What:=searchInput, MatchCase:=False) 'this statement is killing me


If client <> "" Then
matchCountLast = Application.WorksheetFunction.CountIf(lastName, client)

matchCountFirst = Application.WorksheetFunction.CountIf(firstName, client)
End If

Basically my problem is with the line last before the if statement. Sometimes the find will find nothing. I intend it to be that way. I don't wanna use on error resume next without understanding how to properly handle the error.

Little overview, this is personal worksheet I am going to use for storing, entering, and pulling data for potential clients, leads, prospects, etc. Basically it is going to check if the name matches any lasts, then if it matches any firsts. I have the program down for most of it. However I can't figure out how to manuever around this error. Hopefully it isnt syntax bc ive been trying to figure it out for like 24 hours now

Also, if i used .firstName range instead of .lastName. I wouldn't get an error. The fact is i'm testing it. I entered a first name and I want it to check last names first then check first names.

Thanks for your help

 

[Fluff]

I always use a range valrable when using .Find, as you can easily test to see if it was found

    Dim Fnd As Range
    Set Fnd = lastname.Find(searchInput, , , xlWhole, , , False, , False)
    If Fnd Is Nothing Then
        'nothing found
    Else
        'something found
    End If

 

[JayStax]

I always use a range valrable when using .Find, as you can easily test to see if it was found

    Dim Fnd As Range
    Set Fnd = lastname.Find(searchInput, , , xlWhole, , , False, , False)
    If Fnd Is Nothing Then
        'nothing found
    Else
        'something found
    End If

It is not finding something. I expect that. is there an if find feature perhaps? I need it to check a row then check another before it can determine if no data is available.

 

[JayStax]

It is not finding something. I expect that. is there an if find feature perhaps? I need it to check a row then check another before it can determine if no data is available.

From my research thus far, most people suggest doing it exactly how you stated. So I am going to give it a try. I really wanted to use the string. also couldn't edit my original reply.

 

[Fluff]

Simply put the code you want to run if nothing is found, where it says "'nothing found"

 

[JayStax]

Simply put the code you want to run if nothing is found, where it says "'nothing found"

tbh i am not sure what you mean.

heres the code i used going off of your info.

'////////////////////////////// Section for finding and pulling client info ///////////////////////////////////////


searchInput = inputCell.Value

'/////////////////////////////////////////avoid error for finding/////////////////////////////////////////////////
Set avoidError = lastName.Find(What:=searchInput, MatchCase:=False)

If avoidError Is Nothing Then

   Set avoidError = firstName.Find(What:=searchInput, MatchCase:=False)
    
    If avoidError Is Nothing Then
    
    client = ""
    
    Else
    
    client = avoidError.Value
    
    End If
    
Else

client = avoidError.Value


End If
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

If client <> "" Then
matchCountLast = Application.WorksheetFunction.CountIf(lastName, client)

matchCountFirst = Application.WorksheetFunction.CountIf(firstName, client)
End If

 

[Fluff]

And does it work?

 

[JayStax]

And does it work?

so far so good! thanks so much! always appreciate the community on this forum, you guys are da bomb!!!!!

 

[Fluff]

Your welcome, however when using Find I would recommend using it like I showed.
Excel remembers the settings you used last (either manually or in VBA) so if your are looking for "Smith" your code could return "Smithson"