Extract data between dates

[dbnfl]

Hello,

I have the below code and I need to modify date to between dates.

Currently it extracts data greater than date in Cell G3 on report sheet. I am wanting to try and extract data bewteen 2 dates G3 & (H3)

Sub Containers_on_Vessel_Extract()

Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim jobtype As String
Dim VesselETA As Date
Dim Vessel As String
Dim Wharf As String
Dim Agent As String
Dim finalrow As Integer
Dim i As Integer
Dim Ary As Variant

Set datasheet = Sheet1
Set reportsheet = Sheet19


VesselETA = LCase(reportsheet.Range("G3").Value)
Vessel = LCase(reportsheet.Range("C3").Value)
Wharf = LCase(reportsheet.Range("I3").Value)
Agent = LCase(reportsheet.Range("E3").Value)

     
   reportsheet.Range("B7:aa300").ClearContents
  
   datasheet.Select
   finalrow = Cells(Rows.Count, 2).End(xlUp).Row
   For i = 2 To finalrow
        If Cells(i, 3).Value = "Pre Alert" Or Cells(i, 3).Value = "Ok to Slot" Or Cells(i, 3).Value = "Slotted" Or Cells(i, 3).Value = "Delivery Pending" Or Cells(i, 3).Value = "Delivery Confirmed" Or Cells(i, 3).Value = "AQIS" Then
        If LCase(Cells(i, 41)) = Wharf Or Wharf = "" Then
        If LCase(Cells(i, 44)) = Vessel Or Vessel = "" Then
        If LCase(Cells(i, 5)) = Agent Or Agent = "" Then
        If LCase(Cells(i, 42)) >= VesselETA Then
        Ary = Application.Index(Rows(i), 1, Array(2, 3, 4, 5, 6, 8, 10, 16, 58, 167, 18, 19, 57, 34, 35, 41, 42, 43, 44, 46, 47, 49))
        reportsheet.Range("B200").End(xlUp).Offset(1, 0).Resize(, 22).Value = Ary
      End If
      End If
      End If
      End If
      End If
   Next i
  
reportsheet.Select

Range("C3").Select

End Sub

Any help would be greatly appreciated.

Dale.

 

[Sanjeev1976]

Try the following code :

Sub Containers_on_Vessel_Extract()

Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim jobtype As String
Dim VesselETA As Date
Dim Vessel As String
Dim Wharf As String
Dim Agent As String
Dim finalrow As Integer
Dim i As Integer
Dim Ary As Variant

Set datasheet = Sheet1
Set reportsheet = Sheet19

VesselETA = LCase(reportsheet.Range("G3").Value)
VesselETD = LCase(reportsheet.Range("H3").Value)
Vessel = LCase(reportsheet.Range("C3").Value)
Wharf = LCase(reportsheet.Range("I3").Value)
Agent = LCase(reportsheet.Range("E3").Value)

reportsheet.Range("B7:aa300").ClearContents

datasheet.Select
finalrow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To finalrow

If Cells(i, 3).Value = "Pre Alert" Or Cells(i, 3).Value = "Ok to Slot" Or Cells(i, 3).Value = "Slotted" Or Cells(i, 3).Value = "Delivery Pending" Or Cells(i, 3).Value = "Delivery Confirmed" Or Cells(i, 3).Value = "AQIS" Then

If LCase(Cells(i, 41)) = Wharf Or Wharf = "" Then

If LCase(Cells(i, 44)) = Vessel Or Vessel = "" Then

If LCase(Cells(i, 5)) = Agent Or Agent = "" Then

If LCase(Cells(i, 42)) >= VesselETA and LCase(Cells(i, 42)) <= VesselETD Then

Ary = Application.Index(Rows(i), 1, Array(2, 3, 4, 5, 6, 8, 10, 16, 58, 167, 18, 19, 57, 34, 35, 41, 42, 43, 44, 46, 47, 49))

reportsheet.Range("B200").End(xlUp).Offset(1, 0).Resize(, 22).Value = Ary

End If

End If

End If

End If

End If

Next i


reportsheet.Select

Range("C3").Select

End Sub

 

[dbnfl]

If LCase(Cells(i, 42)) >= VesselETA and LCase(Cells(i, 42)) <= VesselETD Then

Perfect thank you very much

 

[Sanjeev1976]

Happy to be of some help and thanks for the feedback