Run

[Ben]

Hi all,

I am Access 2002 sp3. Below is my code from one of the vba module in
Access. Everything works until I reach the line oWksh.Cells.Find, that's
when I got error saying:

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


sub Test (ByRef oWkbk As Excel.Workbook)
Dim dLastRow As Double
Dim oWksh As Excel.Worksheet

Set oWksh = oWkbk.ActiveSheet

oWksh.Range("A1").Select
Selection.End(xlDown).Select
dLastRow = oWkbk.Parent.ActiveCell.Row


oWksh.Cells.Find(What:="FindSomething", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

...
...
...

Set oWksh = Nothing

end sub


I am not sure what is causing the error. Can you share with me what you
notice? Perhaps an extra pair of eyes would find something I miss. Thanks
so much.

Ben



--

 

[strive4peace]

Hi Ben,

not sure ... perhaps you are not passing a valid reference
.... does your code compile okay?

btw, dLastRow should be DIMensioned as long, not double

if you are not sure which line is causing the error, put an
error handler in...

at beginning of code
'~~~~~~~~~~~~~~~~~~~~~~~~
On Error GoTo Proc_Err


'~~~~~~~~~~~~~~~~~~~~~~~~
at end of code

'~~~~~~~~~~~~~~~~~~~~~~~~

Proc_Exit:
On Error Resume Next
'release object variables
Exit Sub

Proc_Err:
MsgBox Err.Description, , "ERROR " _
& Err.Number & " YOURprocedurename"

'press F8 to step through code and debug
'remove or comment next line after debugged
Stop: Resume
Resume Proc_Exit
'~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*