Edit working code to look for value after a date & before a date

[ipbr21054]

Hi,
I have a working code which looks for a value "DELIVERED NO SIG" from todays date & back for 90 days.

I am looking to edit this code so it looks for the value starting from 30 days of todays date & then back 60 days.
So from todays date October 28th the code would start to look for the value from September 28th to July 28th

Originaly straight from todays date & back 90 didnt go to plan so needs to be as mentioned above
@NoSparks does this make sense to you,Thanks


Code shown below

Private Sub UserForm_Initialize()

    Dim fndRng As Range
    Dim firstAddress As String
    Dim cnt As Long
    
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "220;195;110;170;130;50;100"
                    '220;200;110;170;130;60;100"
End With

With Sheets("POSTAGE").Range("G:G")
    Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' check the date
            [COLOR=rgb(184, 49, 47)]If Date - DateValue(fndRng.Offset(, -6)) < 90 Then[/COLOR]
                cnt = cnt + 1
                ' add to listbox
                With Me.ListBox1
                    .AddItem fndRng.Offset(, -5).Value                      'CUSTOMER
                    .List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value    'ITEM
                    .List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value    'DATE
                    .List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value    'TRACKING NUMBER
                    .List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value     'CLAIM
                    .List(.ListCount - 1, 6) = fndRng.Value                 'RECEIVED NO SIG
                End With
            End If
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

If cnt = 0 Then
    MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
    End
End If

    Me.StartUpPosition = 0
    Me.Top = Application.Top + 100  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN

End Sub

 

[Alex Blakenburg]

Do you just mean something like this ?
(You will need to play with the > & < than to see if you want them to be >= & <=)

    Dim elapsedDays As Long
    
    elapsedDays = Date - DateValue(fndRng.Offset(, -6))
    If elapsedDays < 90 And elapsedDays > 30 Then
    
    End If

 

[ipbr21054]

Can you advise what is the difference between,
> & <

>= & <=

 

[ipbr21054]

When i use the > & > the 30 days is fine but the 90 days depends on the date on my worksheet.
I mean if i didnt post on that day which is the 90th day as the code checks for then the code can only check the next dtae to which i have entered on my sheet.
It close enough & will do

Thanks

 

[Alex Blakenburg]

>30 will not include 30 but start at 31
>= will include 30
Same concept for 90
<90 will not include 90 but start at 89
<= will include 90
So it just depends on what you want.

I personally I wouldn't use the VBA Date function in the code but have a reporting date in a cell on the spreadsheet, and use that in the code.
You could put =today() in that reporting date cell to achieve the same thing but it would then give the option of overwriting that and enter a reporting date, in case you actually decide you want to run the report for a different date ie yesterday or last week etc.

 

[ipbr21054]

So ive changed & used the >= & <= now
In cell A1 i have entered =today() & i see todays date shown.

What alteration would i need to make to then use the date in cell A1
Please advise.

Private Sub UserForm_Initialize()

    Dim fndRng As Range
    Dim firstAddress As String
    Dim cnt As Long
    Dim elapsedDays As Long
    
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "220;195;110;170;130;50;100"
End With

With Sheets("POSTAGE").Range("G:G")
    Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do ' check the date
           elapsedDays = Date - DateValue(fndRng.Offset(, -6))
            If elapsedDays <= 90 And elapsedDays >= 30 Then
            
                cnt = cnt + 1
                With Me.ListBox1 ' ADD VALUES TO LISTBOX
                    .AddItem fndRng.Offset(, -5).Value                      'CUSTOMER'S NAME
                    .List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value    'ITEM
                    .List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value    'DATE
                    .List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value    'TRACKING NUMBER
                    .List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value     'CLAIM
                    .List(.ListCount - 1, 6) = fndRng.Value                 'RECEIVED NO SIG
                End With
            End If
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

If cnt = 0 Then
    MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
    End
End If
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 100  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN
End Sub

 

[Alex Blakenburg]

Typically you would have a label in A1 and the date in B1.
eg A1 > Report Date B1 > =Today()
You could use a Named Range for the cell with the date in it.

Use whatever variable name makes sense to you.

            Dim reportDate As Date
            reportDate = Sheets("POSTAGE").Range("A1").Value2
            
            elapsedDays = reportDate - DateValue(fndRng.Offset(, -6))