CODEWITHSUNDEEP
vbaexcelboolean
Motatekashikimimo
Motatekashikimimo

Reputation: 1

VBA check if columns contains specific value

How can I find if columns contain TRUE or FALSE VALUE?

Upvotes: 0

Views: 5938

Answers (4)

ashleedawg
ashleedawg

Reputation: 21619

As an example, you could evaluate cell A1 like this:

Sub Demo()
    Select Case Sheets("Sheet1").Range("A1")
        Case True
            MsgBox "Cell A1 is TRUE"
        Case False
            MsgBox "Cell A1 is FALSE"
        Case Else
            MsgBox "Cell A1 is neither True nor False"
    End Select
End Sub

Edit:

Here is an alternative solution, since perhaps the goal is more of a "T/F Summary" for the entire column:

Sub Demo2()

    Dim cntT As Long, cntF As Long, resp As String
    cntT = WorksheetFunction.CountIf(Sheets("Sheet1").Columns("A"), True)
    cntF = WorksheetFunction.CountIf(Sheets("Sheet1").Columns("A"), False)

    Select Case cntT
        Case Is > 0
            Select Case cntF
                Case Is > 0
                    resp = "a mix of " & cntT & " TRUE and " & cntF & " FALSE."
                Case Else
                    resp = cntT & " TRUE but no FALSE."
            End Select
        Case Else
            Select Case cntF
                Case Is > 0
                    resp = cntF & " FALSE but no TRUE."
                Case Else
                    resp = "neither TRUE nor FALSE."
            End Select
    End Select

    MsgBox "The column contains " & resp, vbInformation

End Sub

Example Output:

img

Upvotes: 4

QHarr
QHarr

Reputation: 84465

=IFERROR(LOOKUP(TRUE,A:A),IFERROR(NOT(LOOKUP(FALSE,A:A)),"NEITHER FOUND"))

Or

=IFERROR(IF(LOOKUP(TRUE,A:A),TRUE,IF(NOT(LOOKUP(FALSE,A:A)),FALSE)),"NEITHER FOUND")

Or 

IF(SUMPRODUCT(--(A:A=TRUE))>0,"True found",IF(SUMPRODUCT(--(A:A=FALSE))>0,"FALSE FOUND",))

Or

Option Explicit
Public Sub Demo()
    Dim arr(), i As Long
    Const COL_NUMBER As Long = 1                     '< =Col A
    arr = Array(True, False)
    For i = LBound(arr) To UBound(arr)
        If Not ActiveSheet.Columns(COL_NUMBER).Find(arr(i)) Is Nothing Then
            MsgBox arr(i) & " found in column " & COL_NUMBER
            Exit Sub
        End If
    Next i
    MsgBox "Neither True not False Found"
End Sub

As a function call (to allow you an easy way to use as UDF in a sheet passing a column in as range argument):

Option Explicit
Public Sub Test()
    Dim rng As Range
    Const COL_NUMBER As Long = 1     '< =Col A
    Set rng = ActiveSheet.Columns(COL_NUMBER)
    MsgBox IsTrueOrFalseFound(rng)
End Sub
Public Function IsTrueOrFalseFound(ByVal rng As Range) As String
    Dim arr(), i As Long
    arr = Array(True, False)
    For i = LBound(arr) To UBound(arr)
        If Not rng.Find(arr(i)) Is Nothing Then
            IsTrueOrFalseFound = arr(i) & " found in column " & rng.Column
            Exit Function
        End If
    Next i
    IsTrueOrFalseFound = "Neither True not False Found"
End Function

Upvotes: 1

Miqi180
Miqi180

Reputation: 1691

Here's a simple one-liner, which returns true if a match was found and false if no match was found:

Debug.Print Not IsError(Application.Match(True, Range("A:A"), 0))

The code above checks for the boolean value True, but if you want to check for the string "True", simply insert "True" in the first parameter instead.

Upvotes: 0

pgSystemTester
pgSystemTester

Reputation: 9907

I think the first two answers posted are too complex based on the succinct requirements the OP lists. A CountIF approach seems like the most efficient approach.

Ignoring the OP title, the answer does not even require VBA. It can just be a cell formula:

Test if True Found:

=IF(COUNTIF(B:B,TRUE)>0,"There's a TRUE in the Column!","No true's found!")

Test if True OR False found:

=IF(COUNTIF(B:B,TRUE)+COUNTIF(B:B,FALSE)>0,"There's either a True or False FOUND!","Nothing found")

If this activity does need to be referenced in VBA, then you can use a similar approach.

Dim SrchRNG As Range
Set SrchRNG = Range("B:B")

If Application.WorksheetFunction.CountIf(SrchRNG, True) + Application.WorksheetFunction.CountIf(SrchRNG, False) > 0 Then
    'Here's when a true or false has been found
Else
    'Code if neither is found
End If

Upvotes: 1

Related Questions