How to compare today's date to a cell on VBA?

[Sivangen]

Hi,

I'm trying to create a macro which will:
-Open on the first sheet
-Get today's date
-Compare today's date to a certain cell in the sheet
-If today's date is bigger (=the date on the cell has passed) - it'll move to the next sheet and so on to the next sheets.

I thought you might have some suggestions, anyone?

Thanks,

Sivan.

[Dave Peterson]

Maybe something like:

Option Explicit
Sub auto_open()

Dim myDate As Date
Dim myAddr As String
Dim wCtr As Long

myDate = Date
myAddr = "a1"

For wCtr = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(wCtr).Range("a1").Value <= myDate Then
Application.Goto Worksheets(wCtr).Range("a1"), Scroll:=True
Exit For
End If
Next wCtr

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sivangen wrote:
>
> Hi,
>
> I'm trying to create a macro which will:
> -Open on the sheet1
> -Get today's date
> -Compare today's date to a certain cell in the sheet
> -If today's date is bigger (=the date on the cell has passed) - it'll
> move to the next sheet and so on to the next sheets.
>
> I thought you might have some suggestions, anyone?
>
> Thanks,
>
> Sivan.
>
> --
> Sivangen
> ------------------------------------------------------------------------
> Sivangen's Profile: http://www.excelforum.com/member.php...o&userid=34925
> View this thread: http://www.excelforum.com/showthread...hreadid=546614

--

Dave Peterson

[Tom Ogilvy]

Hello Dave,

Shouldn't it be

If Worksheets(wCtr).Range("a1").Value >= myDate Then

I thought he said move on when the date in the sheet had passed.

>-If today's date is bigger (=the date on the cell has passed) - it'll
move to the next sheet and so on to the next sheets.


--
Regards,
Tom Ogilvy



"Dave Peterson" wrote:

> Maybe something like:
>
> Option Explicit
> Sub auto_open()
>
> Dim myDate As Date
> Dim myAddr As String
> Dim wCtr As Long
>
> myDate = Date
> myAddr = "a1"
>
> For wCtr = 1 To ThisWorkbook.Worksheets.Count
> If Worksheets(wCtr).Range("a1").Value <= myDate Then
> Application.Goto Worksheets(wCtr).Range("a1"), Scroll:=True
> Exit For
> End If
> Next wCtr
>
> End Sub
>
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Sivangen wrote:
> >
> > Hi,
> >
> > I'm trying to create a macro which will:
> > -Open on the sheet1
> > -Get today's date
> > -Compare today's date to a certain cell in the sheet
> > -If today's date is bigger (=the date on the cell has passed) - it'll
> > move to the next sheet and so on to the next sheets.
> >
> > I thought you might have some suggestions, anyone?
> >
> > Thanks,
> >
> > Sivan.
> >
> > --
> > Sivangen
> > ------------------------------------------------------------------------
> > Sivangen's Profile: http://www.excelforum.com/member.php...o&userid=34925
> > View this thread: http://www.excelforum.com/showthread...hreadid=546614
>
> --
>
> Dave Peterson
>

[Dave Peterson]

That seems like a very reasonable suggestion <bg>.

Thanks for the correction.

Tom Ogilvy wrote:
>
> Hello Dave,
>
> Shouldn't it be
>
> If Worksheets(wCtr).Range("a1").Value >= myDate Then
>
> I thought he said move on when the date in the sheet had passed.
>
> >-If today's date is bigger (=the date on the cell has passed) - it'll
> move to the next sheet and so on to the next sheets.
>
> --
> Regards,
> Tom Ogilvy
>
> "Dave Peterson" wrote:
>
> > Maybe something like:
> >
> > Option Explicit
> > Sub auto_open()
> >
> > Dim myDate As Date
> > Dim myAddr As String
> > Dim wCtr As Long
> >
> > myDate = Date
> > myAddr = "a1"
> >
> > For wCtr = 1 To ThisWorkbook.Worksheets.Count
> > If Worksheets(wCtr).Range("a1").Value <= myDate Then
> > Application.Goto Worksheets(wCtr).Range("a1"), Scroll:=True
> > Exit For
> > End If
> > Next wCtr
> >
> > End Sub
> >
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Sivangen wrote:
> > >
> > > Hi,
> > >
> > > I'm trying to create a macro which will:
> > > -Open on the sheet1
> > > -Get today's date
> > > -Compare today's date to a certain cell in the sheet
> > > -If today's date is bigger (=the date on the cell has passed) - it'll
> > > move to the next sheet and so on to the next sheets.
> > >
> > > I thought you might have some suggestions, anyone?
> > >
> > > Thanks,
> > >
> > > Sivan.
> > >
> > > --
> > > Sivangen
> > > ------------------------------------------------------------------------
> > > Sivangen's Profile: http://www.excelforum.com/member.php...o&userid=34925
> > > View this thread: http://www.excelforum.com/showthread...hreadid=546614
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Sivangen]

Thanks guys, you've helped me a lot!