Code for checking "Error" & "#N/A" in specific columns and returning the columns with issue (if the case)

[IosifFlorin]

Hello everyone,

as the title mentions, i am trying to check for specific error words in column P -> R & T (starting with row 5 for all).
I was looking on the internet and i managed to bring together the below code.

- I am trying now to figure out how can i check all these columns and return text when for example only column Q and T have the error words ( so based on where the issue, to receive a message with the specific column/columns).
- if there is no error words, than at the end a message of "ok" suffices

Thanks for your help, let me know in case some additional information would be required.


Lastrow = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row With Sheets("Sheet1").Range("Q5:Q" & Lastrow)

Set check1 = .Find("#N/A", LookIn:=xlValues)
If check1 Is Nothing Then
MsgBox "Column Q ok"
Else
MsgBox ("Check Business Unit (Column Q)!!!")
End If
End With

 

[Joe4]

Try this:

Sub MyErrorCheck()

    Dim Lastrow As Long
    Dim Myrow As Long
    
    Lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For Myrow = 5 To Lastrow
        If Application.WorksheetFunction.IsNA(Cells(Myrow, "Q")) Then
            MsgBox ("Check Business Unit (Column Q)!!!")
            Exit Sub
        End If
    Next Myrow
    
    MsgBox "Column Q ok"

End Sub

 

[IosifFlorin]

thanks Joe for the code, unfortunately it's not solving what i wanted to implement:

- check cells in columns P -> R & T for specific words like "#N/A", "Error"
- return text with the columns where the specific words are found or "all is ok"

 

[Joe4]

Sorry, I misunderstood, and was focusing more on fixing up your code and missed some key details in your question.

I think this may do what you are looking for:

Sub MyErrorCheck()

    Dim Lastrow As Long
    Dim Myrow As Long
    Dim myColumns As Variant
    Dim i As Byte
    Dim myErrorMessage As String
    
'   Set array with columns to check
    myColumns = Array("P", "Q", "R", "T")
    
'   Use column A to find last row with data
    Lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
'   Loop through all columns in array
    For i = LBound(myColumns) To UBound(myColumns)
'       Loop through all rows
        For Myrow = 5 To Lastrow
            If Application.WorksheetFunction.IsError(Cells(Myrow, myColumns(i))) Then
'               Add error message to string
                myErrorMessage = myErrorMessage & "Check Business Unit (Column " & myColumns(i) & ")!!!" & vbCrLf
                Exit For
            End If
        Next Myrow
    Next i
    
'   Check to see if any errors
    If myErrorMessage = "" Then
        myErrorMessage = "All columns are OK!"
    End If
    
'   Return message box
    MsgBox myErrorMessage


End Sub

 

[IosifFlorin]

thank you very much Joe, this is it
no need to apologize! thanks for your help

is it much more complex if i want to look for specific words? ( you can take whichever example of words as you wish, just want to understand the principle/way to do it)

 

[Joe4]

You would probably want to set up another array that holds your list of words, and loop through that for each cell, sort of like this here: http://www.mrexcel.com/forum/excel-...-using-find-list-words-not-just-one-word.html

If you do it that way, you can probably eliminate the need to loop through each cell/row, and just use the FIND functionality on an entire column at a time.

Note that if you are looking for "words", the words returned by errors may not work they way you think (i.e. the "#N/A" error that is returned is not the same thing as the text string "#N/A").

 

[IosifFlorin]

thanks
i copy / paste values + use iferror and that's why i asked + for future cases when i actually have to look for specific words