Help needed with a macro

[Victor Delta]

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]

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" <[email protected]>
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

 

[Victor Delta]

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

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 Cone]

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" <[email protected]>
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

 

[Victor Delta]

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]

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" <[email protected]>
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!

 

[Victor Delta]

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]

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


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

 

[Victor Delta]

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

Thanks again,

V