Cite Cell Value in A MsgBox

[nalij]

Thanks in advance to anyone that can offer me assistance in trying to figure this out. I have been searching for awhile now and cannot seem to get this to work.

End Goal: Search column O (dynamic dataset) for the word/value "Review", if found in any row, throw the user a message box with the cell address of that row(s). If the word/value "Review" is not found in column O then the message box would read "No Errors Founds".

So if "Review" is found in the column the message box would read as:
- "Errors found in row(s) 13, 38, 55. Please review your entries."

Code Developed Thus Far:

   Dim TestCell As Range   Dim MsgReview As String
   Dim MsgGood As String


    MsgReview = "Error found in row(s) <cell.row>, please review your entries"
    MsgGood = "No errors found"
    
    Application.ScreenUpdating = False


    For Each TestCell In [O1].EntireColumn.Cells
        ' break on first find
        If TestCell = "Review" Then
            MsgBox Replace(MsgReview, "<cell>", .Row.Address)
            Exit For
            'msg = msg & "Errors Found in " & cell.Address(False, False) & " please review your entries" & vbNewLine
            Exit For
        End If
    Next TestCell
 
   Application.ScreenUpdating = True

I know there are a bunch of errors in this code right now. Such as the syntax for citing the row, the lack of ability to throw up the positive message if no errors are found, the layout of how I am using the messagebox.

Again, I really appreciate any help that can be provided.

THANKS IN ADVANCE!</cell></cell.row>

 

[Nathan_Excel]

Hi,

This is possible.

What i would do is have a function in a module, we'll say Get_Error_Cells

something like

Public Function Get_Error_Cells(rng As Excel.Range, strError As String) As String
Dim lngRangeRow As Long
For lngRangeRow = 1 To rng.Rows.Count - 1
If rng.Cells(lngRangeRow, 1).Value = strError Then
Get_Error_Cells = Get_Error_Cells & IIf(Len(Get_Error_Cells) > 0, ",", "") & rng.Cells(lngRangeRow, 1).Address
End If
Next lngRangeRow
End Function


to use this you would have

msgbox ("Errors are shown in : " & Get_Error_Cells(Range("A1:A20"), "review")


Hope this helps.

 

[wigi]

Hello there

Here's interesting and very efficient code:

Sub LoopThroughRows()

    sCells = "O2:O8"
    
    sRows = Join(Filter(Evaluate("transpose(if(" & sCells & "=""Review"",ROW(" & sCells & "),""#""))"), "#", False), ", ")
    
    If Len(sRows) Then
        MsgBox "Errors found in row(s) " & sRows & ". Please review your entries.", vbCritical
    Else
        MsgBox "No errors found.", vbInformation
    End If


End Sub

Change the address ot the cells to be looked for (for example, make the range dynamic). Now it is range O2:O8.

 

[nalij]

Wigi your code worked beautifully, thank you so much!

And I made it dynamic using a LastRow function.

 

[wigi]

You're welcome, thanks for the like !