bg18461
02-09-2011, 02:29 PM
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?
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?