check if worksheet column M does not contain "CNF" then MsgBox

[d_carr]

my question:

within macro code,
i want to check if my worksheet "Cost of Work - operations" in column M "System Status"
does not contain "CNF",
then MsgBox "System Status must contain CNF",
else continue executing code.
-end statement

 

[sulakvea]

Sub whatever()

With Sheets("Cost of Work - operations").Range("M:M")
 Set c = .Find("CNF", LookIn:=xlValues, lookat:=xlPart)
 If c Is Nothing Then MsgBox ("System Status must contain CNF")
End With

End Sub

 

[d_carr]

sulakvea,

thanks but that didnt work. the MsgBox didnt appear, even though cell M3 didnt contain CNF.

my data will be a dynamic number of rows (always changing), how do i make sure the code is only looking at rows with data and not all 65536 rows?

also my searchable data is from cell M2 downwards, as cell M1 is column header "System Status", so i need to search from cell M2 downwards.

thanks

 

[John Davis]

sulakvea,

thanks but that didnt work. the MsgBox didnt appear, even though cell M3 didnt contain CNF.

my data will be a dynamic number of rows (always changing), how do i make sure the code is only looking at rows with data and not all 65536 rows?

also my searchable data is from cell M2 downwards, as cell M1 is column header "System Status", so i need to search from cell M2 downwards.

thanks

Will this help??

Sub CNF()
Dim i As Long
Dim LASTROW As Long

LASTROW = Cells(Rows.Count, 13).End(xlUp).Row

For i = LASTROW To 2 Step -1

Sheets("Sheet2").Select
    If Range("M" & i) <> "CNF" Then
        MsgBox ("System Status must contain CNF")
        Exit Sub
    End If
        
Next i

End Sub

 

[sulakvea]

the msg box will not appear if column M does contain CNF. if the msg box didn't appear, it means column M contains CNF somewhere in it. i tested it and it works fine.

plus, i added a little condition (lookat:=xlPart) to the search - meaning that it will look for CNF anywhere in the cell. i.e. MFFCNF would also yield a positive. if you would like to find cells with only CNF in them - replace the lookat:=xlPart part with lookat:=xlWhole.

also i wouldnt worry about searching thru the entire column M since FIND is executed in native code. it's much faster than going thru each cell and checking the contents. it wont slow down your macro a bit.

you can double check my code by manually finding CNF in Col M (do a ctrl+F on col M and see if it contains CNF). as i said i tested it and it works fine. let me know how it goes.

 

[doofusboy]

Dim i As Long
Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "M").End(xlUp).Row
    For i = 1 To lngLastRow
        If InStr(Range("M" & i).Value, "CNF") > 0 Then
            ' "CNF" found in cell
        Else
            ' "CNF" not found in cell
            MsgBox Range("M" & i).Address & "  System Status must contain CNF"
        End If
    Next i

 

[d_carr]

im running the macro button from sheet "Summary Sheet".
i've set the focus to Sheets("Cost of work - operations").Select
and now the macro is almost working 100% on the below code, but this code is still looking at cell M1 column header and i dont want it to. i want it to look at cell M2:Mxxx.

what code do i insert to search from cell M2:Mxxx ??

code so far:
Dim i As Long
Dim lngLastRow As Long
Sheets("Cost of work - operations").Select
lngLastRow = Cells(Rows.Count, "M").End(xlUp).Row
For i = 1 To lngLastRow
If InStr(Range("M" & i).Value, "CNF") > 0 Then
' "CNF" found in cell
Else
' "CNF" not found in cell
MsgBox Range("M" & i).Address & " System Status must contain CNF"
End If
Next i

 

[d_carr]

got the above working by changing:
For i = 1 To lngLastRow
to
For i = 2 To lngLastRow.

next question re below code:
im currently checking if value "1" exists. how do i check if value "1" or "2" exists?
what is the code to do that?

current code:
'error check priority
Dim i_two As Long
Dim lngLastRow_two As Long
Application.ScreenUpdating = False
Sheets("Task Completion - operations").Select
lngLastRow_two = Cells(Rows.Count, "H").End(xlUp).Row
For i_two = 2 To lngLastRow_two
If InStr(Range("H" & i_two).Value, "1") > 0 Then
' "1" found in cell
Else
' "1" not found in cell
MsgBox "Priority must contain '1' in Cell Number " & Range("H" & i_two).Address
Exit Sub
End If
Next i_two

 

[d_carr]

the above is now working