Error using Find function

[bg18461]

I am trying to find the intersect between a row string and a column date but I keep getting error: Runtime Error 91: Object variable or With block variable not set

This is the data on the excel spreadsheet i'm using:
http://www.forumimagecodes.com/images/b1t952nifysayusg6igy.png (http://www.forumimagecodes.com/)

This is the code i'm using:



'Code on sheet with values
Sub test()
MsgBox fxIntersect2("9310", DateValue("1/3/2011"))
End Sub

'Code in a module
Public Function fxIntersect2(LookupHead As String, LookupDate As Date) As Variant
Dim RowNum As Long
Dim ColNum As Long
ColNum = Rows(1).Find(LookupHead, after:=Cells(1, 1)).Column
Debug.Print ColNum
Debug.Print LookupDate
RowNum = Columns(1).Find(LookupDate, after:=Cells(1, 1)).Row


If RowNum > 0 And ColNum > 0 Then

fxIntersect2 = Cells(RowNum, ColNum).Value



When I step through the code, the error happens on the "RowNum" line, the ColNum is fine. Any ideas?

[Tinbendr]

RowNum = Sheet1.Columns(1).Find(LookupDate, after:=Cells(1, 1), LookIn:=xlValues).Row
The find was probably failing. Maybe it was looking at date serial number and not the string value of the cell.

From the help file.

Remarks The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

David

[bg18461]

Thanks for the reply but I am still getting the same error. Any other ideas?

[Tinbendr]

Sorry, I don't. As soon as I inserted the Lookin:=xlValues, it started working. Up until then, it failed just as you said.

I think I formatted the dates column as dates and the headers as text.

David