CODEWITHSUNDEEP
excelvba
ianf
ianf

Reputation: 39

Excel VBA - Check cells in a column do not contain the string 8254 and display MsgBox

I am trying to put some vba code together to check if the contents in every cell from L2 down to the last row of data (until a blank cell is found) does not contain the string '8254' The number codes in column L are 27 characters long and always contain '8254' as the last 4 digits. So if I can verify '8254' in every cell then the format/code is correct (some number codes are incorrect and need to be investigated). If the string '8254' is not present in one or more of the cells, display a MsgBox warning there are error(s) in the column.

I have been trying to make the below code work, however I am new to vba and need some help

Does anyone know how I can do this?

Thanks

Sub CheckCodes()

'Check column 'L' for Non Standard Number Codes
Dim code As Range

 For Each code In Range("L2", Range("L" & Rows.Count).End(xlUp))
    If VBA.Right$(VBA.Trim$(code), 4) <> "8254" Then
        MsgBox "Non Standard Number Codes Found!  " & vbNewLine & "Check Number Codes  ", , "ADVISORY!"
        Exit Sub
    End If
Next code
End Sub

Upvotes: 0

Views: 1818

Answers (2)

ianf
ianf

Reputation: 39

I found the issue was that the code was checking a number of blank cells down to line L1000 and causing the issue. I modified as per below so it only checks to the bottom of the data and it is working fine. Thanks for all your kind help and comments.

Sub CheckCodes()

'Check column 'L' for Non Standard Number Codes
Dim code As Range

 For Each code In Range("L2", Range("L" & Rows.Count).End(xlUp))
    If VBA.Right$(VBA.Trim$(code), 4) <> "8254" Then
        MsgBox "Non Standard Number Codes Found!  " & vbNewLine & "Check Number Codes  ", , "ADVISORY!"
    Exit Sub
End If
Next code
End Sub

Upvotes: 1

Alex P
Alex P

Reputation: 12487

Using right function (assumes check is that 8234 are always last four digits):

Sub CheckCodes()
    Dim code As Range

    For Each code In Range("L2:L1000")
        If VBA.Right$(VBA.Trim$(code), 4) <> "8234" Then
            MsgBox "Non Standard Number Codes Found!  " & vbNewLine & "Check Number Codes  ", , "ADVISORY!"
            Exit Sub
        End If            
    Next code
End Sub

Upvotes: 0

Related Questions