Comparing dates in a worksheet

[rinijg]

Hi, I am writing a code in which the user enters 2 dates, start date and end date. There is a database workbook, where each worksheet represents a project. What I have to find out is the name of the projects whose starting date is more than start date that the user entered and the ending date is less than the end date.These project names should be displyed as result in another workbook, hyperlinked to the corresponding sheets in the datebase workbook.
Following is the code that I wrote for that.

       Dim date1 As Date
    Dim date2 As Date
Dim start_date As Date
Dim end_date As Date
Dim starting As Range
Dim finishing As Range
If date1 = Null Or date2 = Null Then
   Else
    For Each sh In DestBook.Worksheets
      
      
   If sh.Range("B25").Value <= date1 And sh.Range("B26").Value >= date2 Then
   
  start_date = sh.Range("B25").Value
  end_date = sh.Range("B26").Value
  Set starting = sh.UsedRange.Find(What:=start_date, LookIn:=xlValues)
  Set finishing = sh.UsedRange.Find(What:=sh.Range("B26").Value, LookIn:=xlValues)
   
   shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & starting.Parent.Name & "'" & "!" & starting.Address, _
        TextToDisplay:=sh.Name
        Exit Sub
        End If
End If

But this code is not working. Could someone please help me in this? Please

 

[ukmikeb]

Hi

If the User entered start date is "date1" and the User entered end date is "date2" does the following statement check your criteria?

   If sh.Range("B25").Value <= date1 And sh.Range("B26").Value >= date2 Then

and should it not be -

 If sh.Range("B25").Value => date1 And sh.Range("B26").Value <= date2 Then

hth

 

[rinijg]

Hi, Thanks for the reply.

Actually

   If date1 = "" Or date2 = "" Then
   Else
    For Each sh In DestBook.Worksheets
      
      
   If sh.Range("B25").Value <= date1 And sh.Range("B26").Value >= date2 Then
 start_date = sh.Range("B25").Value
  end_date = sh.Range("B26").Value

till here it works perfectly, when I showed the declarations as below

   Dim date1 As Variant
    Dim date2 As Variant
Dim start_date As Variant
Dim end_date As Variant
Dim starting As Range
Dim finishing As Range

But,

  With sh.UsedRange
  Set starting = .Find(What:=start_date, LookIn:=xlValues)
  Set finishing = .Find(What:=end_date, LookIn:=xlValues)
   
   shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & starting.Parent.Name & "'" & "!" & starting.Address, _
        TextToDisplay:=sh.Name
        Exit Sub
        End With
        End If

this part of code is where error is coming.
when i put breakpoints at these lines, I culd figure out that 'starting' value is nothing and 'finishing' value is nothing.
Also, the hyperlink part is showing some error.

I tried all possible ways. But I am unable to figure out what to do...

 

[ukmikeb]

Hi

What I was trying to point out was that if the User entered -
Start date 1st Feb 2011 (date1) and End date 28th Feb 2011 (date2)
and therefore wanted to select all projects in February.

If the project starting date was 2nd Feb 2011 (B25) and ending date was 15th Feb 2011 (B26) then the statement -

   If sh.Range("B25").Value <= date1 And sh.Range("B26").Value >= date2 Then

would fail in both cases!

Also, you are missing a "Next" for the following statement -

    For Each sh In DestBook.Worksheets

hth