Range.Find Issues

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:

Code:

---

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

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

It might help if you

Code:

---

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

Code:

---

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

---