If statement not working properly

[Felicity Geronimo]

I have written the following code but it doesnt work, please can you
help?

Sub CSV_Dept_Checker()

'selects column F
Sheets("Dept").Columns(6).Select

If (Cells.Select <> Null) Or (Cells.Select <> "Y") Then

MsgBox "Data is formatted correctly"

Else

MsgBox "Incorrect data"

End If

End Sub

Basically all i want to do, is once a file has been creted, run the
above code and I want the code to let me know if the cell contains
anything other that nothing or "Y". If Null or "Y" then give me
message "Data is formatted correctly" if anything else "Incorrect
data"

Why does this not work?

Thanks
Felicity

 

[JulieD]

Hi Felicity

you say "I want the code to let me know if the cell contains .."
however you're selecting a column and not a cell
do you want to check all the cells in the column - if so, you will need to
cycle through each cell and test it
or do you want to select one cell and check that?

Cheers
JulieD

 

[Guest]

A null value is not equal to anyother value, not even another null value.
Therefore the relational operators do not work.

You should use the IsNull function.

 

[Felicity Geronimo]

Hi,

I do want to check each cell in the selected column (apart from the
first cell which is the column heading)Is this easy?

Regards

Felicity

 

[JulieD]

Hi Felicity

I've included two macros both use the range F2 to F100 - just change the 100
to the number of rows to check.
the first checks each cell and gives you a message for each cell
---
Sub checkF()
Set myrange = Sheets("Data").Range("F2:F100")
For Each cell In myrange
If cell.Value = "" Or cell.Value = "Y" Then
MsgBox "Data formatted correctly " & cell.Address
Else
MsgBox "Incorrect Data " & cell.Address
End If
Next
End Sub
----

the second checks the whole range and gives you a message at the end - if
anything other than null or y is found in any cell you get the incorrect
data statement, otherwise you get the correct data message
---

Sub checkF2()
Set myrange = Sheets("Data").Range("F2:F20")
i = 0
For Each cell In myrange
If cell.Value = "" Or cell.Value = "Y" Then
Else
i = i + 1
End If
Next
If i > 1 Then
MsgBox "Incorrect Data"
Else
MsgBox "Data formatted correctly"
End If
End Sub