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

d_carr

New Member
Joined
May 12, 2010
Messages
15
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
Code:
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
 
Last edited:

d_carr

New Member
Joined
May 12, 2010
Messages
15
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

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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??
Code:
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

Well-known Member
Joined
Jul 2, 2008
Messages
994

ADVERTISEMENT

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.
 
Last edited:

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Code:
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

New Member
Joined
May 12, 2010
Messages
15

ADVERTISEMENT

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

New Member
Joined
May 12, 2010
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,019
Messages
5,575,588
Members
412,678
Latest member
ruturajs7rs
Top