error 91 (Object variable or with block variable set)

[Ricardo Caicedo]

I am having an error 91 in red (Object variable or with block variable set) and I have no idea if the object is correct, as I am newbie in VBA.<o></o>
<o> </o>
This program (which I made from other examples) tries to copy and paste information from different worksheets to the current worksheet. However, the location for the first cell to copy in each one of files to be copied is variable. So, I need to open each file to be copies and “look for” the first row to be copied.<o></o>
<o> </o>
In order to look for the first row where the data are, all the files have the word “rate” as a header in the data, but you have to look three times from the cell A1 to step into this header and next go to the next row (as there is another occurrence of this word before be the header). So if for example, the data to be copied starts in the cell A25, the cell A23 have the header "Rate", however other file could nave the header at cell A21.

So I need to look for "Rate" three times and go to the next row.
<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 453.75pt; HEIGHT: 395.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_5 type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="" src="file:///C:\Users\rcaicedo\AppData\Local\Temp\msohtmlclip1\01\clip_image001.emz"></v:imagedata></v:shape><o></o>
Please see down here the code and the breakpoint when is failing. Can you please some body help me .

<o>
Sub Consolidate()
'Open all Excel files in a specific folder and import data into Burn Repot Tab
Dim fName As String
Dim wbkOld As Workbook, wbkNew As Workbook
Dim rRNG As Range
Dim eEmptyS As Boolean

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set wbkNew = ThisWorkbook
ChDir "H:\My Documents\Industrials Supp\14A"

fName = Dir("Burn_Report_FortisBC*.xl*")
wbkNew.Activate
'Import first active sheet from found file
Do While Len(fName) > 0
Set wbkOld = Workbooks.Open(fName)
wbkOld.Activate
'Sheets(1).Activate
ActiveSheet.Cells.Find(What:="Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveSheet.Cells.FindNext(After:=ActiveCell).Activate
ActiveSheet.Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("Burn Reports").Activate 'assumes data starts in A1
Set rRNG = Cells(1, 1).CurrentRegion
If rRNG.Cells.Count = 0 Then
'no data in master sheet
eEmptyS = True
Else: eEmptyS = False
End If
If eEmptyS Then
ActiveSheet.Paste
Else:
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveSheet.Paste
End If
fName = Dir
wbkOld.Close False

Loop

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


</o>

 

[Joe4]

You usually get the "error 91" message on Find VBA code when it cannot find the value you are looking for. So it sounds like it is not finding the word "Rate" in your range.

So, you need to add VBA code to handle that error. You can either ignore it, or "catch" it and do something about it (such as return a message to the screen).

Chip Pearson did a nice write-up on ignoring/handling errors in VBA code here: Error Handling In VBA

 

[Ricardo Caicedo]

Thank you. then, I think that the problem is that the program is looking in the wrong excell spreadsheet. How can I be sure that the program open the correct sheet...

Somedoby can help me as I debug the program and stops in the file and I am 100% sure that the word"Rate exist"

 

[Joe4]

The file will open to whatever page was opened when it last saved. You have no control over that.
What you can do is to select a specific sheet after you open it and before you run your find code (like that 'Sheets(1).Activate code your have commented out).

You can either select a sheet by its name:

Sheets("Sheet1").Activate

or by reference:

Sheets(1).Activate

(this last code would select the first sheet in the workbook, regardless of what its name is).