[RESOLVED] VBA Count Columns given

[IGPOD]

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:


Here's what I'm trying to accomplish:


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.

[vbfbryce]

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

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.

[IGPOD]

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:

[IGPOD]

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!

[vbfbryce]

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