I have an excel file for showing a report by month and cumulative data.
First tab is an Entry sheet. Row 4 lists the last day of the month starting in E4 and Quarter# every 3rd cell.
E4 is a date formula (=DATE(A3,7,31)) and A3 is the current year YYYY format.
F4 is an formula (=EOMONTH(E4,1))
G4 is an formula (=EOMONTH(F4,1))
H4 is Text Q1
I4 is an formula (=EOMONTH(G4,1))
and so on


On the monthly tabs J7 is a formula to pull the same month end as the sheet. IE: Jul tab shows "07/31/2010" with the formula pointing to the Entry sheet cell that is for July (='Entry sheet'!E4)

I execute my code on the monthly tab. What I am trying to do is set the cursor on the corresponding month end for the sheet on the entry sheet. From there I do some checks on numbers then come back to the monthly sheet. when I step through the process "MyCurrentDate" fills with the correct date as a sting.

my problem is I keep getting a "Run-time error '91' Object Variable or With block variable not set " error on the find statement and I have no idea why

Dim MyCurrentDate As String
MyCurrentDate = Range("J7").Value
CurrentSheet = ActiveSheet.Name
Sheets("Entry sheet").Select
Rows("4:4").Find(What:=MyCurrentDate, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Any Thoungts or suggestions?

Rows("4:4").Find what should this be selecting and on what sheet?

does this help?

Dim MyCurrentDate As String
MyCurrentDate = Range("J7").Value
currentsheet = ActiveSheet.Name
Sheets("Entry sheet").Select
Range("A1:D4").Select
Selection.Find(What:=MyCurrentDate, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

Nope same error. The Find was supposted to find the matching date to the sheet it was comming from. After more research I think it has to do with the way Find is handling the Date value in MyCurrentDate. So I had to right the code to remove the find.

This is what I ended up having to do.

...
Sheets("Entry sheet").Select
Range("E4").Select
ActiveCell.Offset(0, Application.Match(CLng(MyCurrentDate), Range("E4:S4"), 0)).Range("A1").Select