CODEWITHSUNDEEP
excelvba
NicKitty
NicKitty

Reputation: 35

Count all rows that are within a date range

I'm attempting to count the number of rows in my sheet that meet 3 sets of criteria: the client name in column C matches my active row; the due date in column G; and column M is blank (indicating no previous submission was sent).

I can get this to work just fine with the following code:

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Broker Workflow")

Dim i As Long
Dim iVal As Long

Dim lastRow As Long: lastRow = ws.Range("C" & Rows.Count).End(xlUp).Row
Dim strClient As String: strClient = Cells(ActiveCell.Row, "C").Value
Dim strRenDate As String: strRenDate = Cells(ActiveCell.Row, "G").Value
Dim strNotSubmitted As String: strNotSubmitted = ""

Dim strCriteria As String: strCriteria = strClient & strRenDate & strNotSubmitted

iVal = 0
    
    For i = 8 To lastRow
        If ws.Range("C" & i).Value & ws.Range("G" & i).Value & ws.Range("M" & i).Value = strCriteria Then
            iVal = iVal + 1
        End If
   Next i
   
Dim strCount As String: strCount = iVal

My problem is that now I want to extend this to count all rows with a due date that is within a range of my active row date +/- 7 days (14 day range). So if my due date is 07/06/2020 it will count the number of rows that match my client name in C, have blank cell in M and a date of anything between 01/06/2020-14/06/2020 in G.

Upvotes: 0

Views: 66

Answers (1)

Corrado Barbero
Corrado Barbero

Reputation: 301

You are making it more complicated than needed... can get rid of the four variables above, and simply test like this:

For i = 8 To lastRow
    If ws.Range("C" & i).Value = Cells(ActiveCell.Row, "C").Value & _
        ws.Range("M" & i).Value = "" & _
        ws.Range("G" & i).Value >= DateAdd(Cells(-7, "d", ActiveCell.Row, "G").Value) & _
        ws.Range("G" & i).Value <= DateAdd(Cells(7, "d", ActiveCell.Row, "G").Value) Then
         iVal = iVal + 1
    End If
Next i

[EDIT] Sorry! I have no idea what I wrote earlier.

I mixed up the parameters in DateAdd and used & instead of and

This works, tested:

For i = 8 To lastRow
        If Cells(i, "C").Value = Cells(ActiveCell.Row, "C").Value And _
            Cells(i, "M").Value = "" And _
            Cells(i, "G").Value >= DateAdd("d", -7, Cells(ActiveCell.Row, "G").Value) And _
            Cells(i, "G").Value <= DateAdd("d", 7, Cells(ActiveCell.Row, "G").Value) Then
             iVal = iVal + 1
        End If
Next i

It will also work with your ws.Range syntax, it was just simpler for me to test it like this with Cells Please note that the current line also gets counted if it has an empty M column...

Upvotes: 1

Related Questions