Error using "Cells.Find(What:="Vendor Fare".....)" VBA Excel

It gives Error 91 if the "Vendor Fare" does not exist in that sheet.

That's because the Find returns Nothing, and Nothing.Column doesn't make sense.

But why does the Find return nothing? Column 13 has "Vendor Fare"  as the header
Thanks

ok GOT Your error !!!
when you get the error check what is the string that you have it may be something like:
' Vendor  Fare' (note between Vendor and Fare you have 2 spaces and not 1. reason why you get this error !!!

If you need a fix let me know.
gowflow

This is your fix the system will warn you if the header is not found and will exit the routine.

Sub FindFare()
'This routine loops through a column of values and counts the number of errors in the column

Dim rowHeader As Integer
Dim colFare As Integer
Dim rowActive As Integer
Dim ErrCount As Integer
Dim cCell As Range

'-------
'Idenitfy Header Row and Relevant Column
Set cCell = Cells.Find(what:="Client_Code", after:=Cells(1, 1), lookat:=xlWhole)
If Not cCell Is Nothing Then
    rowHeader = cCell.Row
Else
    MsgBox ("Please check your header it should be 'Client_Code' routine will exit")
    Exit Sub
End If
'colFare = 13
Set cCell = Cells.Find(what:="Vendor Fare", after:=Cells(1, 1), lookat:=xlWhole)
If Not cCell Is Nothing Then
    colFare = cCell.Column
Else
    MsgBox ("Please check your header it should be 'Vendor Fare' routine will exit")
    Exit Sub
End If
'Set active row for Loop
rowActive = rowHeader
'-------
'Count Errors
ErrCount = 0
'Loop through data
Do Until Cells(rowActive, colFare) = ""
    If Cells(rowActive, colFare) Like "Err?*" Then
        ErrCount = ErrCount + 1
    End If
    rowActive = rowActive + 1
Loop
'-------
'Print the error count
Debug.Print ErrCount

End Sub

Select allOpen in new window

pls chk the attached file.
gowflow
RunTime-91-error-with-FIND-V01.xlsm

Sorry but seems you missed something !!!
Did you see my post ????

Reason why you had an Error 91 is simply because it did not find the occurrence 'Vendor Fare' and exacly like this. The version I posted will prevent this error. You could have had this error simply because inadvertently a space was either placed between the 2 words or at the beginning.

Did you check my solution ??
gowflow

Yes, indeed I checked your post; in my original file, there was only one space between "Vendor" and "Fare".  I tried it several ways, both copying and pasting the actual text into the vba routine; I tried typing it in; I tried copying and pasting from the vba routine to the spreadsheet, so as to ensure that the text being searched  was available.  I tried, in the worksheet, itself, to compare the two strings with "=Vendor Fare = X6" .... and so on.  Apologies for not commenting on your post.

Cheers
David

ok fine what you tried. But the way your routine is you may run into this situation (error 91) in the future.
UNLESS
if you change the code for the one I posted.

Your choice.
gowflow

I understand completely that I need error checking code there and I appreciate what you have written.  My puzzlement was that I could see no reason for the error in the first place. In fact, the error itself was an error!

I thought i was missing something obvious, but the machine was  playing its own game.

Again, my apologies for not giving your comment more credit.
David

Still your not getting it !!!!

Do not care about the points.

the code the way it is will generate error if for some reason 'Vendor  Fare' or 'Client_Code' are not strictly written like this. and to prevent that is the code I posted. you should never have a statement like you have:

rowHeader = Cells.Find(what:="Client_Code", after:=Cells(1, 1), lookat:=xlWhole).Row
as this has no escape but possibly produce an error if the searched string is not found.

hope above clarifies.
gowlfow

Ok, I thought I understood what you are saying.

As I understand it you are saying that my routine is a dead end and will produce an error if the exact form of my search is not present. I agree with that.

The thing is, your code is great and will return a message to the user.
The problem, as I see it, is that if the machine is mistakenly causing an error, then it does not matter how many messages are generated, if the solution is not a solution.  Does that make sense?

i'm trying to understand what you are telling me.
Thanks for taking the time to help.

The problem, as I see it, is that if the machine is mistakenly causing an error, then it does not matter how many messages are generated, if the solution is not a solution.  Does that make sense?

What do you mean by that ??
The data get changed all byitself without human intervention ? or you mean to say this data is populated by some automated routine that sometimes does not populate exactly this same strings ??

there is a solution for your problem but first you need to tell me if yes you agree there is a problem or no it was just a mishap and you needed to reboot your machine?

gowflow

gowflow.... I think that it is a problem with the machine, not the code, or the data.

It was a mishap and worked when I rebooted the machine.


I rebooted the pc and the instruction worked, as it should.  The data is correct and the code is correctly reflecting that data.

I now have a different problem, which seems to underline my problem.

Overnight, my pc had automatic MS updates installed.  It shut down my pc and retarted it.  Now, none of my activex controls are working at all and I am unable to insert new activex objects.  This is most disturbing.  I am awaiting my IT manager to roll back my pc to pre-update settings.

Thanks again for your efforts.

Uninstall Office and re-install.
gowflow

That's next on the agenda. Thanks!

All I can say is D'oh! I shall never comprehend the myriad ways that computers find to stop you in your tracks