I use a macro which includes the following code to locate the cell in row 1
which contains today's date.

Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Sheet1").Range("1:1")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If

I now want to do the same on another spreadsheet where row 1 consists
instead of cells containing dates of just the 1st of each month (i.e.
monthly columns). The macro works well on the first each of month but, of
course, cannot find anything on the other days!

Can anyone please suggest how I can amend the code so it locates the cell
containing the same month and year as today. I have tried a number of
options, but none seem to work.

Thanks,

V

A different approach...
Sub IsItThere()
Dim FindString As Date
Dim rng As Range
Dim rCell As Range

FindString = Date
Set rng = Sheets("OtherSheet").Range("1:1").Cells

For Each rCell In rng
If Month(rCell.Value) = Month(FindString) Then
If Year(rCell.Value) = Year(FindString) Then
Application.Goto rCell, True
MsgBox rCell.Address
Exit Sub
End If
End If
Next
MsgBox "Nothing found"
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Victor Delta"
wrote in message
I use a macro which includes the following code to locate the cell in row 1
which contains today's date.

Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Sheet1").Range("1:1")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If

I now want to do the same on another spreadsheet where row 1 consists
instead of cells containing dates of just the 1st of each month (i.e.
monthly columns). The macro works well on the first each of month but, of
course, cannot find anything on the other days!
Can anyone please suggest how I can amend the code so it locates the cell
containing the same month and year as today. I have tried a number of
options, but none seem to work.
Thanks,
V

"Jim Cone" wrote in message
...
A different approach...
Sub IsItThere()
Dim FindString As Date
Dim rng As Range
Dim rCell As Range

FindString = Date
Set rng = Sheets("OtherSheet").Range("1:1").Cells

For Each rCell In rng
If Month(rCell.Value) = Month(FindString) Then
If Year(rCell.Value) = Year(FindString) Then
Application.Goto rCell, True
MsgBox rCell.Address
Exit Sub
End If
End If
Next
MsgBox "Nothing found"
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Jim

Many thanks for your help.

However, I've pasted your macro into my sheet (changing 'other sheet' as
appropriate) but, when run it, I get a run-time error '13' - Type mismatch,
with the debugger highlighting this line:

If Month(rCell.Value) = Month(FindString) Then

Any ideas?

V

All data in the first row must be valid date entries.
If you have a mixed bag, then the cell value must be
confirmed as a date before the comparison is made...
'----
For Each rCell In rng
If IsDate(rCell.Value) Then '<<< new line
If Month(rCell.Value) = Month(FindString) Then
If Year(rCell.Value) = Year(FindString) Then
Application.Goto rCell, True
MsgBox rCell.Address
Exit Sub
End If
End If
End If '<<< new line
Next
'---
If the data in the first row cannot be read as dates, then
another approach is necessary (or you have to change the data).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(please don't bottom post)


"Victor Delta"
wrote in message
Jim
Many thanks for your help.
However, I've pasted your macro into my sheet (changing 'other sheet' as
appropriate) but, when run it, I get a run-time error '13' - Type mismatch,
with the debugger highlighting this line:
If Month(rCell.Value) = Month(FindString) Then
Any ideas?
V

Jim

Many thanks again, that's done it!

The final icing on the cake would be if the sheet then scrolled a few
columns to the left so that this month's column ended up in the middle of
the screen. I though I could do this myself using goto or offset, but again
I have failed totally. Don't suppose you can come to the rescue again
please?

Regards,

V

PS Interesting that you prefer top-posting (just like emails). I usually get
told off when I top-post!


"Jim Cone" wrote in message
...

All data in the first row must be valid date entries.
If you have a mixed bag, then the cell value must be
confirmed as a date before the comparison is made...
'----
For Each rCell In rng
If IsDate(rCell.Value) Then '<<< new line
If Month(rCell.Value) = Month(FindString) Then
If Year(rCell.Value) = Year(FindString) Then
Application.Goto rCell, True
MsgBox rCell.Address
Exit Sub
End If
End If
End If '<<< new line
Next
'---
If the data in the first row cannot be read as dates, then
another approach is necessary (or you have to change the data).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(please don't bottom post)


"Victor Delta"
wrote in message
Jim
Many thanks for your help.
However, I've pasted your macro into my sheet (changing 'other sheet' as
appropriate) but, when run it, I get a run-time error '13' - Type
mismatch,
with the debugger highlighting this line:
If Month(rCell.Value) = Month(FindString) Then
Any ideas?
V

V,
'---
Application.Goto rCell, True
ActiveWindow.ScrollColumn = Application.Max(rCell.Column - 3, 1)
'---
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Victor Delta"
wrote in message
Jim
Many thanks again, that's done it!
The final icing on the cake would be if the sheet then scrolled a few
columns to the left so that this month's column ended up in the middle of
the screen. I though I could do this myself using goto or offset, but again
I have failed totally. Don't suppose you can come to the rescue again
please?
Regards,
V
PS Interesting that you prefer top-posting (just like emails). I usually get
told off when I top-post!


"Jim Cone" wrote in message
...

All data in the first row must be valid date entries.
If you have a mixed bag, then the cell value must be
confirmed as a date before the comparison is made...
'----
For Each rCell In rng
If IsDate(rCell.Value) Then '<<< new line
If Month(rCell.Value) = Month(FindString) Then
If Year(rCell.Value) = Year(FindString) Then
Application.Goto rCell, True
MsgBox rCell.Address
Exit Sub
End If
End If
End If '<<< new line
Next
'---
If the data in the first row cannot be read as dates, then
another approach is necessary (or you have to change the data).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(please don't bottom post)

Jim

You are a star. Many thanks.

The macro works perfectly now. However, I have changed the name to
Auto_Open() and curiously it does not run automatically when I open the
spreadsheet. Most odd.

Regards,

V

"Jim Cone" wrote in message
...

V,
'---
Application.Goto rCell, True
ActiveWindow.ScrollColumn = Application.Max(rCell.Column - 3, 1)
'---
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Victor Delta"
wrote in message
Jim
Many thanks again, that's done it!
The final icing on the cake would be if the sheet then scrolled a few
columns to the left so that this month's column ended up in the middle of
the screen. I though I could do this myself using goto or offset, but
again
I have failed totally. Don't suppose you can come to the rescue again
please?
Regards,
V
PS Interesting that you prefer top-posting (just like emails). I usually
get
told off when I top-post!


"Jim Cone" wrote in message
...

All data in the first row must be valid date entries.
If you have a mixed bag, then the cell value must be
confirmed as a date before the comparison is made...
'----
For Each rCell In rng
If IsDate(rCell.Value) Then '<<< new line
If Month(rCell.Value) = Month(FindString) Then
If Year(rCell.Value) = Year(FindString) Then
Application.Goto rCell, True
MsgBox rCell.Address
Exit Sub
End If
End If
End If '<<< new line
Next
'---
If the data in the first row cannot be read as dates, then
another approach is necessary (or you have to change the data).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(please don't bottom post)

V,
Auto_Open will not run if the workbook is opened via code.
See the RunAutoMacros method.
Jim Cone


"Victor Delta" wrote in message ...
Jim
You are a star. Many thanks.
The macro works perfectly now. However, I have changed the name to
Auto_Open() and curiously it does not run automatically when I open the
spreadsheet. Most odd.
Regards,
V

Oh! Inserted the necessary line and all is now 100%.

Thanks again,

V

"Jim Cone" wrote in message
...
V,
Auto_Open will not run if the workbook is opened via code.
See the RunAutoMacros method.
Jim Cone


"Victor Delta" wrote in message
...
Jim
You are a star. Many thanks.
The macro works perfectly now. However, I have changed the name to
Auto_Open() and curiously it does not run automatically when I open the
spreadsheet. Most odd.
Regards,
V