Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement not working properly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement not working properly
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 "Felicity Geronimo" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement not working properly
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement not working properly
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" wrote in message ... 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 "Felicity Geronimo" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement not working properly
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 --- hope this helps Cheers JulieD "Felicity Geronimo" wrote in message om... 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" wrote in message ... 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 "Felicity Geronimo" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula not working properly | Excel Worksheet Functions | |||
vlookup not working properly | Excel Discussion (Misc queries) | |||
IF statement for time data not working properly | Excel Worksheet Functions | |||
But not working properly | Excel Discussion (Misc queries) | |||
Rowsource not working properly :( | Excel Programming |