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
Microsoft ExcelVisual Basic Classic
19 Comments4 Solutions1070 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
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 Phelops

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

Join our community to see this answer!
Unlock 4 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros