Extract data between dates

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
59
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)

Rich (BB 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)
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.
 
Last edited by a moderator:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
320
Office Version
  1. 365
Platform
  1. Windows
Try the following code :

VBA 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
 

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
320
Office Version
  1. 365
Platform
  1. Windows
Happy to be of some help and thanks for the feedback
 

Forum statistics

Threads
1,181,081
Messages
5,927,970
Members
436,581
Latest member
Luceroso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top