[RESOLVED] VBA Count Columns given

I'm developing a procurement report that given a specific scenario, the VBA will count the number of occurences within a given column and provide me a number of records which match this criteria, it would also be helpful if the VBA could provide a list of the records which match the criteria.

Example: I want an IF function in VBA that will search the "W" column for an "x", if it is empty, I would like it count all the occurences of "180 Days" in column X and then provide that number on Sheet1 into a cell and under it a reference to the content that is in the respective D column (which will show the name of the contract).

Here's the part that has be perplexed, I haven't figured out a way to not get redundancy from the reports. The cells X through AA in record 11 will show up on all respective reports, I only need it to appear once: in the 30 days report as it is set to expire within 30 days.

Here's what it looks like:
Attachment 124989

Here's what I'm trying to accomplish:
Attachment 124991

If you have any suggestions or a way to simplify what I'm requesting of your abilities, please provide anything, I'm open to learning.

Thanks in advance.

Assuming you can find the bottom of your data by looking for the last filled cell in column AB:

Code:

---

Sub countExpiry()
    Dim wb As Workbook
    Dim wsDetail As Worksheet
    Dim wsSummary As Worksheet
    Dim lr As Long
    Dim j As Long
    Dim cnt180 As Integer
    Dim cnt90 As Integer
    Dim cnt60 As Integer
    Dim cnt30 As Integer
    Dim cntCurrent As Integer
    Dim writeRow As Long
   
    Set wb = ActiveWorkbook
    Set wsDetail = wb.Worksheets("detail")
    Set wsSummary = wb.Worksheets("summary")
   
    lr = wsDetail.Range("ab" & Rows.Count).End(xlUp).Row
   
    With wsDetail
        For j = 6 To lr
            If .Range("w" & j).Value <> "x" Then
                If .Range("aa" & j).Value = "30" Then
                    cnt30 = cnt30 + 1
                    writeRow = wsSummary.Range("k" & Rows.Count).End(xlUp).Row + 1
                    wsSummary.Range("k" & writeRow).Value = .Range("d" & j).Value
                ElseIf .Range("z" & j).Value = "60" Then
                    cnt60 = cnt60 + 1
                    writeRow = wsSummary.Range("h" & Rows.Count).End(xlUp).Row + 1
                    wsSummary.Range("h" & writeRow).Value = .Range("d" & j).Value
                ElseIf .Range("y" & j).Value = "90" Then
                    cnt90 = cnt90 + 1
                    writeRow = wsSummary.Range("e" & Rows.Count).End(xlUp).Row + 1
                    wsSummary.Range("e" & writeRow).Value = .Range("d" & j).Value
                ElseIf .Range("x" & j).Value = "180" Then
                    cnt180 = cnt180 + 1
                    writeRow = wsSummary.Range("b" & Rows.Count).End(xlUp).Row + 1
                    wsSummary.Range("b" & writeRow).Value = .Range("d" & j).Value
                Else
                    cntCurrent = cntCurrent + 1
                End If
            End If
        Next j
    End With
    With wsSummary
        .Range("c12").Value = cnt180
        .Range("f12").Value = cnt90
        .Range("i12").Value = cnt60
        .Range("l12").Value = cnt30
        .Range("o12").Value = cntCurrent
    End With
End Sub

---

My two worksheets are named "summary" and "detail" for example.

vbfbryce,

You're something amazing... It works, however, I had to manipulate it a tad of course to fit my sheet names but the lists are going into the columns starting in row 2... how can I get it to start on row 13 instead... this is what it looks like:
Attachment 124995

vbfbryce,

I figured it out... I saw you were counting up so I placed the "#" in the box to initiate the start point... thanks a million, you're amazing!

You're welcome (and I forget to mention that I put the "#" and a "0" in row 12 before running the code!).