Solved: Need help "Run-Time error 91"

[wolf.stalker]

To start, thanks your help!

Let me start by saying that I wrote this code months ago (4-6) and the only thing that I know has changed was that two weeks ago we upgrade from office 2003 to office 2007 (which I am also fixing other issues thanks to this upgrade) but this one has me stumped.

I can't put all the code here, but let me show you what I can and see if you can help.

LoadDate ' var passed by val

(2) If LoadDate > #8/29/2010# Then
(3) MsgBox ("This date is out of range")
(4) Exit Sub
(5) End If

now, here is the wierd part. if the date is before 11/01/2009, this all works perfectly. if the date is 11-1, 11-2, 11-3, 11-4, 11-5, 11-6, 11-7, 11-8, 11-9 then i get the error message on line (6) <Object variable or With block variable not set>. as far as i can tell (and i tested random dates beyond 11/10/2009 it works fine :-\ This process gets run daily and has done so w/o error from 8/31/2009 to present.

The sheet it's looking to find the date on line (6), all the dates are formated the same as just plain text values, not date or number.

Why in the hell would it bug here between 11-1 and 11-9 ?

(6) If Worksheets(3).Range("B" & iLcv) = "UNL" Then
(7) Cells.Find(What:=LoadDate, After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

Thanks for any help you can provide.

[RolfJ]

How did you declare LoadDate? As Date or as String? And what is iLcv? It might be best if you were to post a little more of your code, or better yet the whole workbook with the misbehaving code.

[wolf.stalker]

um, yeah it's actually hitting more than one workbook.

LoadDate is a string.
iLcv is a counter declared as Integer

iLcv is used as the row number on a worksheet when used as If Worksheets(3).Range("B" & iLcv)

[RolfJ]

You must have meant to say that the code blows up on statement (7), not (6), but regardless, I don't have a clue as to the reason.

Sorry.

[rbrhodes]

Hi ws,

Well I set up a sheet exactly how you described and voila there was the problem! Why, can't tell you. However after a bit of experimenting this worked for me consistently:

[VBA]

'//Added to convert string
loaddate = DateValue(loaddate)
'//End

If loaddate > #8/29/2010# Then
MsgBox ("This date is out of range")
Exit Sub
End If

'//Changed Lookin to formulas
'//Change Lookat to whole

Cells.Find(What:=loaddate, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate


[/VBA]

[wolf.stalker]

Thanks RB for your .02. I have tried that but it does not seem to work for me. As a short term fix, i am having to change my dates to future dates i know work (like 12/01/2009), then copy all that and paste it where it's supposed to go. it sucks, but it's a lot better than having to do by hand!

this is like the 3rd thing i have had to fix in two weeks that used to work in 2003 but no longer seem to work in 2007 :-\

[Bob Phillips]

How about

[vba]

If Worksheets(3).Range("B" & iLcv) = "UNL" Then
Cells.Find(What:=CLng(loaddate), After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End If
[/vba]

[wolf.stalker]

How about

[vba]

If Worksheets(3).Range("B" & iLcv) = "UNL" Then
Cells.Find(What:=CLng(loaddate), After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End If
[/vba]

X:

i copied and pasted directly into my workbook and no-go. same error message.