Excel - validating a column of cells?

[alexthecheese]

Hi, I'm trying to write a small formula that scans a small adjacent True/False array - 10 cells in a single column - to see if any of them say 'False'.

Rather than have umpteen IFs nested, is there a better way to do this?

 

[GravyMonster]

Try something like this:

dim Valid = True
For Each cell In Worksheets("MyWorksheet").Range("A1:A10").Cells
    If cell.Value = "False" 
        Valid = False
    End If
Next

' Do something with Valid...

*Caveat: not tested and it's been a while since I wrote any VBA

 

[dazzerd]

Try something like this:

dim Valid = True
For Each cell In Worksheets("MyWorksheet").Range("A1:A10").Cells
    If cell.Value = "False" 
        Valid = False
    End If
Next

' Do something with Valid...

*Caveat: not tested and it's been a while since I wrote any VBA

You will need a then after "false" for sure. Possibly better to do:

Dim myCell as cell
Dim myWorksheet as Worksheet
Dim myRange as Range
Dim valid as String

Set myWorksheet = thisworkbook.sheets("MyWorksheet")
Set myRange = myworksheet.range("A1:A10")

For Each myCell In myRange.Cells
If myCell.Value = False then
msgbox "There is a False at row " & myCell.row
' or do anything else when it finds a cell with false
Else
' do something here when it finds a true cell
End If
Next

Set myworksheet = nothing
Set myRange = nothing

If all you really need to do is find whether there is a cell in a single array which contains false rather than try to find which cells actually contain false. Then simply do =countif(A1:A10 (i.e. your array range), false) in any cell and if the result is greater than zero you have a false. That assumes you are evaluating the cell as a boolean not a string. If its a string you will need to convert it or stick it in quotation marks.

Edit: or just use the inbuilt validation, and put =false in the formula and you can then highlight all the cells in one go.

 

[fretted]

If all you really need to do is find whether there is a cell in a single array which contains false rather than try to find which cells actually contain false. Then simply do =countif(A1:A10 (i.e. your array range), false) in any cell and if the result is greater than zero you have a false. That assumes you are evaluating the cell as a boolean not a string. If its a string you will need to convert it or stick it in quotation marks.

Dazzerd is right all you need is a formula like below:
=IF(COUNTIF(A:A,FALSE)>0,"There is a false","It's all True")

 

[alexthecheese]

That, my friends, is the shizzle. Thank you.