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
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
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