Comparing dates in a worksheet

E

emmanuel

Joined
May 12, 2011
Messages
20
Reaction score
0
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.
Code: 
       Dim date1 As Date    Dim date2 As DateDim start_date As DateDim end_date As DateDim starting As RangeDim finishing As RangeIf 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 IfEnd If
But this code is not working. Could someone please help me in this? Please
 
Code: 
 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
This is the code
 
Hi Emmanuel,

At first sight, I can not see or evaluate what happen exactly as you specified that the code does not work, but which part of it performs as expected? have you tried debugging this using the visual basic debugger available in the Macro editor? if the data on the spreadsheet is not too confidential , or if you can obfuscate it (or convert to dummy data) would you be able to send me a copy of your file and i will help you offline. you can contact me on (e-mail address removed)

Thanks

Regards,
Sif
 
Back
Top