Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of David Phelops
David PhelopsFlag for United Kingdom of Great Britain and Northern Ireland asked on
Visual Basic ClassicMicrosoft Excel
19 Comments1 Solution1070 ViewsLast Modified:
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

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
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
ASKER CERTIFIED SOLUTION
Avatar of David Phelops
David PhelopsFlag of United Kingdom of Great Britain and Northern Ireland image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answers