Drives me nuts! It seems that when I open a new workbook the default format
for dates (entered as 31/12/2007 for example) will be "General" on sheet1 and
"dd/mm/yyyy" on sheet2. Hence my testing only worked if I started on the
correctly formated sheet!!!! Aaaggh!

I found a work around by linking the data report date to a cell in the
control sheet formatted as "General" to get the serial number. The following
code then converted the search area to serial and back again after the
location was found. I'm greatful for your help in pointing me in the right
direction.

Here it is if anyone is interested.

======================================
Sub FindCurMonth()

Dim CurLocation As Range
Dim addr As String
Dim curDate As String
Dim rng As Range

Application.ScreenUpdating = False

curDate = Sheets("Control").Range("B1").Value
Sheets("Cheops").Rows(13).NumberFormat = "general"

On Error Resume Next
Set CurLocation = Rows(13).Find(What:=curDate, LookIn:=xlValues)
Set rng = Range(CurLocation.Address)
Debug.Print (rng.Address)

Application.ScreenUpdating = True

If Not CurLocation Is Nothing Then
addr = CurLocation.Address
Application.Goto CurLocation.Offset(3, 0).Select
Else
MsgBox "Period Date Not Found"
End If

Sheets("Cheops").Rows(13).NumberFormat = "mmm-yy"

End Sub


--
Jim


" wrote:

Funny - didn't work for me as a string but did as a Variant. Dates are
a pain, especially with Find and Filtering..
Paul

On Feb 5, 12:30 am, Jim G wrote:
I'd tried that previously and it only works if it's a string.

However, I tried the code on a new workbook and it works it it's a string,
formula or date. So, would this indicate there is something in the workbook
that is causing the error.



--
Jim- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -