Array loop through multiple rows/columns, find result, then repeat.. Help Needed!

[jgavlin]

Hi everyone! I'm in the process of taking a 3 part course on VBA, but I have a project I need to complete for work that is a bit out of my depths. I'm a first time poster, so hoping for some guidance! I have a worksheet that lists content barcodes in Col. A, container barcodes in Col. B, the approval for destruction year in Col. C, and the shelf location barcode in Col. D where all columns are sorted by Col. B. Each barcode in Col. A is a content of a single container in Col. B. However, a container may have any number of contents listed and each of those contents may also have differing approval for destruction years, of the cell could be blank. My goal end goal is to find the number of containers where all contents have an approval for destruction year of "2021" and then find the number of containers where 1 or more contents have an approval for destruction year that is anything other than 2021. Then I'd like to add a button named "Generate Box Counts" that will add a header to F1 named "2021 Whole Box Count:" with the first container count listed in F2 and a header in G1 named "2021 Partial Box Count" with the second container count listed in G2.

I've tried coming up with nested for loops, do until loops, counter, offset etc., but I don't have a lot of experience with complex arrays and keep getting stuck. I know how to make the headers and the end result display, I just can't figure out how to get from that sub-range of cells to offset(0,1). I apologize that my code is so unfinished, but if anyone could just help point me in the right direction it would be greatly appreciated! I added what I have so far and attached my data set. Also, I'm on Microsoft Office 365 ProPlus. Thanks in advance!

Example VBA Process.jpg

Option Explicit


Sub TestKCWorkingRetentionPredictions()
Dim nr As Integer, i As Integer, nc As Integer, RetYear As Integer, j As Integer, 
'Dim c As Integer
'Dim Counter As Long
Range("A2").Select
Selection.CurrentRegion.Select
Application.ScreenUpdating = False
RetYear = 2021
nr = Selection.Rows.Count
nc = Selection.Columns.Count
For i = 2 To nr
    For j = 1 To nc
        Do Until Selection.Cells(i, j + 1).Value <> Selection.Cells(i + 1, j + 1).Value

        Loop

     'im not sure where to go from here


End Sub

TestKCWorkingRetentionPredictions.xlsm

[snb]

It can be practival to indicate in the file what the expected result should be.

Sub M_snb()
   Sheet1.Cells(1).CurrentRegion.AutoFilter 3, 2021
   
   For Each it In Sheet1.Cells(1).CurrentRegion.Columns(3).Offset(1).SpecialCells(12).Areas
     For Each it0 In it.Cells
        it0.Offset(, 3) = it0.Offset(, -2).Value
        If it0.Offset(-1, -2) <> it0.Offset(, -2) And it0.Offset(-1) <> it0 Then it0.Offset(, 4) = it0.Offset(, -2).Value & "_" & it0.Offset(-1)
     Next
   Next
   
   Sheet1.Cells(1).CurrentRegion.AutoFilter
End Sub