Excel VBA Loop through Column Range to Mark Section Headers

[Lantern]

I have an Excel parts list in which the part section headers are to be hidden if no parts are selected within the section.

I need to loop through a column range that contains a formula-generated index to create a filter in the adjacent column. The part qty may be in one of many columns, so I created the Index to mark the part rows. Section rows that contain a part qty (as described below) are to be marked with a Y.

I've been testing my loops on a sample file that only shows an Index Column with no part information shown.

A section row starts where an Index Row = 1 and extends to where the next Index Row = 1.

Index Values:
0 = Blank Row (Desired Y/N result = N)
1 = Section Row (Desired Y/N result = Y if any parts in the section have a Qty > 0)
2 = Part Row with Qty = 0 (Desired Y/N result = N)
3 = Part Row with Qty > 0 (Desired Y/N result = Y)

The Index Col always starts at C4 and extends to the last row. There will be nothing in the file past the last parts row.

The Index Column can be a few to hundreds of rows long with zero to many variable length sections.

Suggestions, please.

Thanks.

Sample file below . . .

COUNT TEST 8.xlsm
	B	C	D	E	F	G	H	I
2
3		Formula Generated Index	Desired Y/N VBA Results			Parts Row Index Key	Parts Row Description
4		1	Y			0	Blank
5		3	Y			1	Section Header
6		2	N			2	Blank Part
7		2	N			3	Part w/Qty
8		2	N
9		3	Y
10		3	Y
11		2	N
12		2	N
13		3	Y
14		2	N
15		0	N
16		1	N
17		2	N
18		2	N
19		2	N
20		2	N
21		0	N
22		2	N
23		2	N
24		2	N
25		0	N
26		2	N
27		1	Y
28		0	N
29		3	Y
30		3	Y
31		3	Y
32		3	Y
33		2	Y
34		0	N
35		1	N
36		2	N
37		2	N
38		2	N
39		2	N
40		0	N
41		0	N
42		0	N
43
Sheet1

 

[PeteWright]

@Lantern could you provide your 'working' parts list? And a description of what should actually happen, written from scratch (or summed up) to avoid errors and reading all posts first

Shouldn't take much time to fix.

P.S.: I wrote my code in regard to your coding experience.
Hence the many lines and details (to make it 'readable')

But I can adjust Akuini's code as well to suit your need (I guess)

Please let me know which code you prefer.

 

[Lantern]

Pete,

To be able to start from the top cell in the index column, even if doesn't start with an index =1, AND to be able to adjust which column the Y/N result appears in (I use a few templates and the Y/N column can be a variable number of columns from the index column, and to the right or left of the index column) = Awesome.

Akuini's works except for the output column selection part. That was my miss.

I appreciate your note about my coding experience.

Akuini's code looks wickedly sweet, but your code is clearer to me. So based on my experience, I would go with Pete's.

Thanks

 

[Akuini]

Not sure if this is proper forum etiquette, but I unmarked Akuini's code as the solution.

No problem at all; feel free to choose whichever answer you like best.

the Y/N results column is two columns to the left of the Index column (not one column to the right).

Is column Data (Formula Generated Index) always in col C, so you want the result in col A?
If yes, then change this part:

Range("D4").Resize(UBound(vb, 1), 1) = vb

to this:

Range("A4").Resize(UBound(vb, 1), 1) = vb

But if col Data is not fixed then I can amend the code to suit.

 

[PeteWright]

Here's the updated code, works with your requirements on my test sheet:

Option Explicit

Public Sub IndexSection_Counts_V2()
   Application.ScreenUpdating = False
   
   Dim FirstRow As Long
   Dim LastRow As Long
   Dim SectionStart As Long
   Dim SectionEnd As Long
   Dim Qty As Long
   Dim i As Long
   Dim j As Long
   Dim IdxCol As Integer
   Dim ResCol As Integer
   
' ----------------------------------------
   ' SETTINGS / VARIABLES - change here
   ' Index for Data Column (starting with 1 = Column A, 2 = Column B, ...)
   IdxCol = 3
   ' Index for Column with Y/N results (starting with 1 = Column A, 2 = Column B, ...)
   ResCol = 4
   ' Index of first Row containing Data
   FirstRow = 4
' ----------------------------------------
   
   LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, IdxCol).End(xlUp).Row
   
   For i = FirstRow To LastRow
      SectionStart = i
      SectionEnd = NextSectionIndex(SectionStart, IdxCol, LastRow)
      Qty = 0
      For j = SectionStart To SectionEnd
         Select Case Cells(j, IdxCol)
            Case 0
               Cells(j, ResCol) = "N"
            Case 2
               Cells(j, ResCol) = "N"
            Case 3
               Cells(j, ResCol) = "Y"
               Qty = Qty + 1
         End Select
      Next j

      If Cells(SectionStart, IdxCol) = 1 Then
         If Qty > 0 Then
            Cells(SectionStart, ResCol) = "Y"
         Else
            Cells(SectionStart, ResCol) = "N"
         End If
      End If

      Qty = 0
   Next i

   Application.ScreenUpdating = True
End Sub

Private Function NextSectionIndex(PreviousSectionIndex As Long, CurrentColumn As Integer, LastRow As Long) As Long
   Dim i As Long
   
   For i = PreviousSectionIndex + 1 To LastRow
      If Cells(i, CurrentColumn) = 1 Then
         NextSectionIndex = i
         Exit Function
      End If
   Next i
   
   NextSectionIndex = LastRow
End Function

 

[Lantern]

Akuini,

With your code tips, I see now that I can position my columns anywhere in my final solution. It's very flexible. I moved my test columns to various positions on the test sheet and was able to get it to work every time.

Also, your code handles index values I did not anticipate - specifically blank cells and index values outside of my expected range (>3).

I'm going to do more testing in my actual templates, but I expect your code will work well for me.

Thanks.

 

[Lantern]

Pete,

Your code works great in all position I place my test columns.

It handles blank index values cells. I just added a case statement for any index values outside of my expected range (>3).

Case Is > 3
    Cells(j, ResCol) = "N"
    Qty = Qty + 1

I'm going to do more testing in my actual templates, but I expect your code will work well for me.

Now, my issue is which code (Akuini's or Pete's) to use in my templates. This is certainly a good problem to have.

Thanks

 

[Akuini]

Glad the code works.
I revised the code to enhance its flexibility and tidiness.

Sub Lantern_3()
Dim i As Long
Dim va, vb
Dim flag As Boolean
Dim xStart As Range

Set xStart = Range("C4") 'Data (Formula Generated Index) start at C4, change to suit
va = Range(xStart.Address, Cells(Rows.Count, xStart.Column).End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)
For i = 1 To UBound(va, 1)
    If va(i, 1) = 3 Then vb(i, 1) = "Y" Else vb(i, 1) = "N"
    If va(i, 1) = 1 Then n = i: flag = True
        If flag = True Then
            If va(i, 1) = 3 Then
                vb(n, 1) = "Y"
                flag = False
            End If
        End If
Next
'put the result 2 columns to the left of xStart
xStart.Offset(0, -2).Resize(UBound(vb, 1), 1) = vb
End Sub