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

Macro designed to search through a worksheet, find the header row and the "Vendor Fare" column. This is because the column arrangement and header position are a movable feast from time-to-time  Do not want it hard-coded.

The following Code gives a 91 Error, "Object Variable or With Block variable not set" at the line:
colFare = Cells.Find(what:="Vendor Fare", after:=Cells(1, 1), lookat:=xlWhole).Column

Open in new window


Whole routine:
Option Explicit

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

rowHeader = Cells.Find(what:="Client_Code", after:=Cells(1, 1), lookat:=xlWhole).Row
'colFare = 13
colFare = Cells.Find(what:="Vendor Fare", after:=Cells(1, 1), lookat:=xlWhole).Column
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
Debug.Print ErrCount
End Sub

Open in new window

I am sure there is a simple explanation, but I am blind to it... Can anyone enlighten me, please?
Please see attached file.
Thank you
David
RunTime-91-error-with-FIND.xlsm
David PhelopsAsked:
Who is Participating?
 
David PhelopsConnect With a Mentor Author Commented:
Ok, so......  To all who have helped, thank you very, very much.

I was so puzzled, so, I went to lunch.  I had a think; returned and did what I should have done in the first place.....Rookiest of Rooky mistakes!

I TURNED IT OFF AND TURNED IT BACK ON AGAIN!

It works. No problem!

(That begs the question; "What caused the machine to to react that way?"... i'm not going there today.  Anyone wants to have a go, please do!)

Thanks again, though and points all round!

David
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
 
NorieConnect With a Mentor VBA ExpertCommented:
Try this which looks for 'Vendor Fare' in the header row.
colFare = Application.Match("Vendor Fare", Rows(rowHeader), 0)

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
David PhelopsAuthor Commented:
But why does the Find return nothing? Column 13 has "Vendor Fare"  as the header
Thanks
0
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
It works for me - see attached.

If it doesn't work for you, please attached the spreadsheet.
VendorFare.xls
0
 
gowflowConnect With a Mentor Commented:
Your routine and file are fine I see no problem what so ever. I ran the routine and it found 2 errors printed in the debug window.

I ran it on Excel 2010 and 2007 and it was fine. What version of Excel you have ?
gowflow
0
 
gowflowCommented:
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
0
 
gowflowCommented:
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

Open in new window


pls chk the attached file.
gowflow
RunTime-91-error-with-FIND-V01.xlsm
0
 
gowflowCommented:
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
0
 
David PhelopsAuthor Commented:
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
0
 
gowflowCommented:
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
0
 
David PhelopsAuthor Commented:
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
0
 
gowflowCommented:
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
0
 
David PhelopsAuthor Commented:
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.
0
 
gowflowCommented:

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
0
 
David PhelopsAuthor Commented:
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.
0
 
gowflowCommented:
Uninstall Office and re-install.
gowflow
0
 
David PhelopsAuthor Commented:
That's next on the agenda. Thanks!
0
 
David PhelopsAuthor Commented:
All I can say is D'oh! I shall never comprehend the myriad ways that computers find to stop you in your tracks
0
All Courses

From novice to tech pro — start learning today.