Range.Find Issues

[nikki333]

Hi Folks

I'm trying to use the Range.Find method to get a column number, which seems pretty straight forward. However, the results seem quite random at the moment...that is one time it works and another time I get an error 91. Very odd.

In detail, I've got a sub that's supposed to get said column number and nothing more, like so:


numSpaltePlanHeute = rngPlanDatenSpalten.Rows(numZeilePlanRef).Find(What:=DateValue(Date), _
LookIn:=xlFormulas).Column - numSpaltePlanRef

When calling this procedure on it's own it's working. When calling it from a Worksheet_Activate event it works as well. But when calling it from the Worksheet_BeforeSave sub it may work or throw the error 91. I can't find the logic/pattern behind this behaviour.

Worth mentioning is that the find range contains dates...I've read that the find method can get weird with dates.

Any ideas what I'm missing?

Cheers

[SamT]

Maybe some "dates" are actually Text Strings and won't show if LookIn:=formulas.

It might help if you

Dim Dte As String
Dte = Format(Date, "mm-dd-yy") 'or as normally formatted on the sheet

Then Find Dte and Lookin xlValues, while looking at xlPart

[mana]

Application.Match(CLng(Date), rngPlanDatenSpalten.Rows(numZeilePlanRef), 0)