Find() results in Object variable or With Block variable not set

Question

Thanks for reading my post. I'm new to Excel VBA and have run into a wall debugging a call to Find(). I've gone through several posts on this site and others but so far each fix I've tried has been unsuccessful.

I am writing code to process elements out of financial reports. Each report contains one or more multi-row & multi-column blocks of cells with details describing a project. The size of each block isn't consistent, but each always begins in the top left with "Client Name". So I want to iterate through these blocks keying off that text, then pulling out needed elements.

There's no while loop here yet, as I'm running into the error just setting up the first condition.

Run-time error '91': Object variable or With block variable not set

Here's the section of code from within the Sub, with the error coming in the final line assigning cursorProject:

' store the next report to process
Dim nextReport As String
Dim sourceSheetName As String
Dim sheetSource As Worksheet
nextReport = rptMedia
' copy the worksheet into rptBurn and get that worksheet's name
sourceSheetName = GetSheet(nextReport)
Set sheetSource = Workbooks(rptBurn).Worksheets(sourceSheetName)
sheetSource.Cells.EntireRow.Hidden = False
sheetSource.Cells.EntireColumn.Hidden = False
Workbooks(rptBurn).Activate

' process the sheetSource into sheetCurrent
' set constants
Const constCursorKey As String = "Client Name"
Const constClientColumn As String = "B"
Const constClientNameOffset As Integer = 2
Const constProjectLeft As Integer = 2
Const constProjectRight As Integer = 52
' get range in Client Name column of project entries
Dim cursorStart As Long
Dim cursorEnd As Long
Dim cursorProject As Range
Dim rangeProject As Range
Dim rangeSearch As Range

cursorStart = sheetSource.Columns(2).Find(constCursorKey).Row + constClientNameOffset
' find the last project entry in the sheet 
cursorEnd = sheetSource.Range("B" & Rows.Count).End(xlUp).Row
Set rangeSearch = sheetSource.Range(Cells(cursorStart + 1, constProjectLeft), _
                        Cells(cursorEnd, constProjectLeft))
cursorProject = rangeSearch.Find(What:=constCursorKey, LookIn:=xlValues, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                    SearchFormat:=False)

This is very sloppy currently as parts will be moved out to its own Sub called while iterating over the reports (hence nextReport is hardcoded here to a specific report name). The various constants are fixed parameters of the reports. The unlisted values like "rptBurn" are globals. The GetSheet function works well, but if you want to see it:

Private Function GetSheet(rpt As String) As String
   Workbooks.Open rootPath + rpt
   ActiveSheet.Copy after:=Workbooks(rptBurn).Sheets(Workbooks(rptBurn).Sheets.Count)
   GetSheet = ActiveSheet.Name
   Workbooks(rpt).Close
End Function

I've tried several variations on this. The Locals all look promising up to the error. I set the Hidden properties to False based on another post. I've tried simplifying the call down to the basics and using With, like this:

    Set rangeSearch = Sheets(3).Range("B:B")
    rangeSearch.Select
    With rangeSearch
      cursorProject = .Find("Client Name")
    End With

But I'm always getting an error on cursorProject. There are definitely many "Client Name" entries in the worksheet I'm testing. I put in the Select to verify I'm grabbing the correct range; oddly I find that "B:AX" get highlighted (AX is the rightmost used column in the report) in the simple version, but the selection I expect in the original. Regardless there are "Client Name" instances in either selection--I can select B4 and see "Client Name".

What am I doing wrong?

Please try replacing `cursorProject =` with `Set cursorProject =`. — BrakNicku, Mar 31 '15 at 08:54
A lot of work gone into the question, admirable, and nicely done. But there is a lot of unnecessary, text/information. Please read the [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). The main desription and the last code block where the error occurs, with some additional info, would suffice in my opinion. But, again, I admire your effort. @user3964075 got it btw. The `cursorProject ` is a range. You need the `set` keywort to assignt to it. — kolcinx, Mar 31 '15 at 09:13
Thanks both. Now that you point it out, the error is a forehead-slapper. I was staring at it for too long. And thanks for the pointer to writing the example. I saw several very short questions when searching and wanted to leave enough info. — passingwords, Mar 31 '15 at 16:10
Duplicate of [Object variable or With block variable not set (Error 91)](http://stackoverflow.com/questions/20692280/object-variable-or-with-block-variable-not-set-error-91) ... and of a hundred other questions, easily found with a simple search : http://stackoverflow.com/search?q=[vba]+Object+variable+or+With+Block+variable+not+set — Jean-François Corbett, Apr 01 '15 at 08:41
Jean, yes, and what is not apparent in my post is the multiple other bugs Stackoverflow has solved for me previously via pre-existing posts. This is the first time I didn't find the answer; my ask/no-ask % is well in the single-digits. After multiple dead-ends, I reached the point where I was ready to ask for help. Your search string returned the applicable post as #2 (out of >300 hits), but mine didn't. I appreciate the help from the community and do not take anyone's time for granted. — passingwords, Apr 01 '15 at 20:08

score 1 · Answer 1 · answered Mar 31 '15 at 09:12

Cursorproject is an object variable (range). You can't simply assign a value to an object variable, you have to set it.

dim strSomeTextVarible as string
dim rngSomeCellsObjectVariable as range

strSomeTextVarible = "abc"

set rngSomeCellsObjectVariable = range("a1:c3")

Find() results in Object variable or With Block variable not set

1 Answers1