Check five columns for at least one check mark

[gailb]

I've managed to cobble together some code to check five columns to ensure there is at minimum, one check mark. The check mark is an "a" with Marlett applied.

I know this could probably be more efficient, but in essence, I need to check columns G, J, M, P, and S to ensure there is at least the one check mark. If not, I don't want the procedure to continue. All five columns must have at least the one check mark. The column to one to the left establishes the last row of the range to check.

Code: Select all

Sub CheckColumns()
    
    Dim ColG As Long
    Dim ColJ As Long
    Dim ColM As Long
    Dim ColP As Long
    Dim ColS As Long
    
    With Sheets("Dashboard")
        ColG = WorksheetFunction.CountIf(.Range("G2:G" & .Cells(.Rows.Count, "F").End(xlUp).Row), "a")
        ColJ = WorksheetFunction.CountIf(.Range("J2:J" & .Cells(.Rows.Count, "I").End(xlUp).Row), "a")
        ColM = WorksheetFunction.CountIf(.Range("M2:M" & .Cells(.Rows.Count, "L").End(xlUp).Row), "a")
        ColP = WorksheetFunction.CountIf(.Range("P2:P" & .Cells(.Rows.Count, "O").End(xlUp).Row), "a")
        ColS = WorksheetFunction.CountIf(.Range("S2:S" & .Cells(.Rows.Count, "R").End(xlUp).Row), "a")
    End With
    
    MsgBox ColG + ColJ + ColM + ColP + ColS
    
End Sub

[HansV]

Te sum ColG + ColJ + ColM + ColP + ColS doesn't tell you whether each of the columns has at least one check mark. I'd use

Code: Select all

Sub CheckColumns()
    Dim ColG As Boolean
    Dim ColJ As Boolean
    Dim ColM As Boolean
    Dim ColP As Boolean
    Dim ColS As Boolean
    
    With Sheets("Dashboard")
        ColG = WorksheetFunction.CountIf(.Range("G2:G" & .Cells(.Rows.Count, "F").End(xlUp).Row), "a")
        ColJ = WorksheetFunction.CountIf(.Range("J2:J" & .Cells(.Rows.Count, "I").End(xlUp).Row), "a")
        ColM = WorksheetFunction.CountIf(.Range("M2:M" & .Cells(.Rows.Count, "L").End(xlUp).Row), "a")
        ColP = WorksheetFunction.CountIf(.Range("P2:P" & .Cells(.Rows.Count, "O").End(xlUp).Row), "a")
        ColS = WorksheetFunction.CountIf(.Range("S2:S" & .Cells(.Rows.Count, "R").End(xlUp).Row), "a")
    End With
    
    MsgBox ColG And ColJ And ColM And ColP And ColS
End Sub

If all columns have at least one check mark, you'll get True, otherwise False.

[gailb]

Thanks Hans. I didn't even think about this returning TRUE or FALSE.

So with TRUE or FALSE in mind, can this be turned into a Function where the main procedure would call the Function to return the T or F?

In my test, this seems to work, but is this the correct way to pass the values back-and-forth between the Sub and Function?

Code: Select all

Sub CheckColumns()
    Dim GTG As Boolean
    If ColumnCheck(GTG) Then
        MsgBox "Good to proceed"
    Else
        MsgBox "Not all columns have a choice selected"
    End If
End Sub

Code: Select all

Function ColumnCheck(GTG As Boolean)
    
    With Sheets("Dashboard")
        Dim ColG As Boolean: ColG = WorksheetFunction.CountIf(.Range("G2:G" & .Cells(.Rows.Count, "F").End(xlUp).Row), "a")
        Dim ColJ As Boolean: ColJ = WorksheetFunction.CountIf(.Range("J2:J" & .Cells(.Rows.Count, "I").End(xlUp).Row), "a")
        Dim ColM As Boolean: ColM = WorksheetFunction.CountIf(.Range("M2:M" & .Cells(.Rows.Count, "L").End(xlUp).Row), "a")
        Dim ColP As Boolean: ColP = WorksheetFunction.CountIf(.Range("P2:P" & .Cells(.Rows.Count, "O").End(xlUp).Row), "a")
        Dim ColS As Boolean: ColS = WorksheetFunction.CountIf(.Range("S2:S" & .Cells(.Rows.Count, "R").End(xlUp).Row), "a")
    End With
    
    ColumnCheck = ColG And ColJ And ColM And ColP And ColS

End Function

[HansV]

Yes, excellent! But I'd write the first line of the function as

Code: Select all

Function ColumnCheck() As Boolean

There is no need for an argument GTG. So simply call it as follows:

Code: Select all

    If ColumnCheck Then

[gailb]

That's great Hans. Thank you for the help and explanation.