Searching for FALSE values in an array and returning a list of cell addresses for all FALSE values found

[thelisha]

Good day

This is my first post.

Please assist me in finding the appropriate programming (VBA) or array formula to generate a list of addresses for cells in an array with FALSE values in them. I have a sheet comparing values on a similar sheet and a third sheet with TRUE and FALSE values to show me whether Sheet 1 and Sheet 2 differs.

I have 44 columns and 4730 rows of data and multiple FALSEs in each row and column. I would like a list of these cells (their addresses) and a bonus would be the Sheet 1 and Sheet 2 values of each of these cells with their FALSE results.

I have tried a combination of CELL, "address", INDEX and MATCH all to no avail.

Please feel free to contact me should you have any queries on the above.

Thanks in advance.
Kind regards

Thelisha

 

[Wessie]

Hi Thelisha

Welcome to the thread.

This does exactly what you need:

Public LastCell As String
Public Rng As Range
Public LastRow, LastCol As Long


Sub FalseCounter()
Dim a, b, ErrCount As Integer


ErrCount = 0 'Initializing the Error Count.


Call LastCellDetermine 'Finding the last cell in the FALSE TRUE sheet.


For a = 1 To LastCol 'Running from first to last column in the data
    For b = 1 To LastRow 'Running from first to last row in the data
        If Sheets(2).Cells(b, a).Formula = "FALSE" Then 'Finding all FALSE values in Sheet 2 (Your False True sheet)
           ErrCount = ErrCount + 1 'If a False is found the error count is increased.
           Sheets(1).Cells(ErrCount + 1, 1).Formula = Sheets(2).Cells(b, a).Address 'Entering the address and values from your other sheets.
           Sheets(1).Cells(ErrCount + 1, 2).Formula = Sheets(3).Cells(b, a).Value
           Sheets(1).Cells(ErrCount + 1, 3).Formula = Sheets(4).Cells(b, a).Value
        End If
    Next b
Next a


Sheets(1).Activate
Sheets(1).Cells(1, 1).Select
MsgBox ErrCount & " FALSE values were found.", vbOKOnly, "Done" 'A message is displayed with your number of errors.


End Sub


Function Last(Rng As Range) 'Finding the last cell in the False True sheet


        On Error GoTo 0


        On Error Resume Next
        LastRow = Rng.Find(What:="*", _
                       After:=Rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0


        On Error Resume Next
        LastCol = Rng.Find(What:="*", _
                        After:=Rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0
        
        On Error Resume Next
        Last = rng1.Parent.Cells(lrw1, lcol1).Address(False, False)
        If Err.Number > 0 Then
            Last = rng1.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0
End Function


Sub LastCellDetermine()


    ' Use all cells on the False True sheet
    Set Rng = Sheets(2).Cells 'Defining your sheet with false's on them.
    LastCell = Last(Rng) 'Calling the last cell determiner.
    
End Sub

Feel free to contact me should you have any queries.

Kind regards
Wessie

 

[AlanY]

a formula alternative

Excel 2012
	A	B	C	D
1	8	8	5	10
2	9	9	7	8
3	1	10	3	4
4	9	8	10	10
5	8	3	10	5
6	3	2	10	9
7	3	1	8	1
8	1	2	6	2
9	9	6	4	3
10	1	9	5	8
Sheet1

Excel 2012
	A	B	C	D
1	3	4	8	2
2	1	2	8	8
3	1	4	2	5
4	3	6	9	10
5	3	1	5	9
6	10	4	5	2
7	10	5	8	4
8	5	9	5	10
9	2	9	4	5
10	2	9	3	7
Sheet2

Excel 2012
	A	B	C	D
1	FALSE	FALSE	FALSE	FALSE
2	FALSE	FALSE	FALSE	TRUE
3	TRUE	FALSE	FALSE	FALSE
4	FALSE	FALSE	FALSE	TRUE
5	FALSE	FALSE	FALSE	FALSE
6	FALSE	FALSE	FALSE	FALSE
7	FALSE	FALSE	TRUE	FALSE
8	FALSE	FALSE	FALSE	FALSE
9	FALSE	FALSE	TRUE	FALSE
10	FALSE	TRUE	FALSE	FALSE
Sheet3

Excel 2012
	F	G	H	I
1
2				$D$2 = 8
3	$A$3 = 1
4				$D$4 = 10
5
6
7			$C$7 = 8
8
9			$C$9 = 4
10		$B$10 = 9
Sheet3

Cell Formulas
Range	Formula
F1	=IF(A1,CELL("address",A1)&" = "&INDIRECT("Sheet1!"&CELL("address",A1)),"")