I'm trying to check a range of cells for the value "X" and when the column name where the "X" was found is among an array I have previously specified, I want to have a helper column that would say TRUE otherwise say FALSE.

To illustrate, here's a sample table:

In my sample, I have this array that contains 3 values ( Math, English and History). If there is an X in any of the rows whose header name is in the array, I want the helper column to say TRUE otherwise FALSE. It doesn't have to be all of the values in the array, it can be at least only one.

Here is my code (my original file has more columns than my sample, so my code is liek this)

Sub add_helper()
             ' Adding helper column
             Dim checking As Variant
             checking = check_issue() -- this is another function, basically checking will contain the values I want to check in this case Math, English and History, i have confirmed this gets it successfully
             Dim wks As Worksheet
             Set wks = ActiveSheet
            
                Dim rowRange As Range
                Dim colRange As Range
            
                Dim LastCol As Long
                Dim LastRow As Long
                LastRow = wks.Cells(wks.Rows.Count, "I").End(xlUp).row
            
                Set rowRange = wks.Range("I2:AD" & LastRow)
                Set colRange = wks.Range("I1:AD1")
            
                'Loop through each row
                For Each rrow In rowRange
                Do
                        For Each cell In colRange
                            'Do something to each cell
                            If InStr(checking, cell.value) > 0 Then
                                If Cells(rrow.row, rrow.Column).value <> "" Then
                                    wks.Range("AI" & rrow.row).value = "TRUE"
                                    Exit For
                                Else
                                    wks.Range("AI" & rrow.row).value = "FALSE"
                                End If
                            End If
                        Next cell
                Loop Until wks.Range("AI" & rrow.row).value <> "TRUE"
                Next rrow
                
                
           
             
End Sub

My code results to just having an input of true whenever there is an X without actually checking if the header column is in my array.

CodePudding user response：

Did you try normal formulas in Excel? You could create a table (a ListObject) with the courses as your array values and the combine SUMPRODUCT with COUNTIF to output True/False in your helper column. Easy to update and adapt:

Notice the table at most right named T_COURSES. The formula in helper column is:

=SUMPRODUCT(--(COUNTIF(T_COURSES,$B$1:$E$1)>0)*--(B2:E2="x"))>0

It works perfectly and it autoupdates changing values:

CodePudding user response：

Match Headers of Matches Against Values in Array

Option Explicit

Sub AddHelper()
    
    Dim checking As Variant: checking = check_issue()
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim hrg As Range: Set hrg = ws.Range("I1:AD1") ' Header Range
    
    Dim drg As Range ' Data Range
    Set drg = ws.Range("I2:AD" & ws.Cells(ws.Rows.Count, "I").End(xlUp).Row)
    
    Dim crg As Range: Set crg = drg.EntireRow.Columns("AI") ' (Helper) Column Range
    crg.Value = False
    
    Dim rrg As Range, rCell As Range, r As Long, c As Long, IsFound As Boolean
    
    For Each rrg In drg.Rows
        r = r   1 ' for the (helper) column range
        c = 0 ' for the header range
        For Each rCell In rrg.Cells
            c = c   1
            If StrComp(CStr(rCell.Value), "x", vbTextCompare) = 0 Then
                If IsNumeric(Application.Match(CStr(hrg.Cells(c)), checking, 0)) _
                    Then IsFound = True: Exit For
            End If
        Next rCell
        If IsFound Then crg.Cells(r).Value = True: IsFound = False
    Next rrg
    
End Sub