Solved

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

Posted on 2014-12-09
19
399 Views
Last Modified: 2014-12-14
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
0
Comment
Question by:David Phelops
  • 8
  • 8
  • 2
  • +1
19 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488743
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
 
LVL 33

Assisted Solution

by:Norie
Norie earned 167 total points
ID: 40488747
Try this which looks for 'Vendor Fare' in the header row.
colFare = Application.Match("Vendor Fare", Rows(rowHeader), 0)

Open in new window

0
 

Author Comment

by:David Phelops
ID: 40488752
But why does the Find return nothing? Column 13 has "Vendor Fare"  as the header
Thanks
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 167 total points
ID: 40488766
It works for me - see attached.

If it doesn't work for you, please attached the spreadsheet.
VendorFare.xls
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 166 total points
ID: 40488779
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40488801
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40488811
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
 

Accepted Solution

by:
David Phelops earned 0 total points
ID: 40488971
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40489033
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:David Phelops
ID: 40489051
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40489059
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
 

Author Comment

by:David Phelops
ID: 40489080
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40489158
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
 

Author Comment

by:David Phelops
ID: 40489168
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40489412

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
 

Author Comment

by:David Phelops
ID: 40491060
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40491130
Uninstall Office and re-install.
gowflow
0
 

Author Comment

by:David Phelops
ID: 40491150
That's next on the agenda. Thanks!
0
 

Author Closing Comment

by:David Phelops
ID: 40498674
All I can say is D'oh! I shall never comprehend the myriad ways that computers find to stop you in your tracks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now