Msgbox to display multiple results if they occur

[Masun]

Hi all,

Hope someone can help me with this. I have a msgbox problem that I cant solve on my own. In my set of data i would like a code that checks if there in column Z is the word "Missing" and if so displays the values from the cells on the same row but columns H and I in a msgbox. If there are any "Missing" in col Z i need to stop running the code after a click on a OK-Button. The prompt for the msgbox is "Something seems to be missing, check out:"

For each row that there is "Missing" i col Z i would like one row in the msgbox with the values from cells H and I in the same row. The number of rows can vary. The columns are from A - AA.

I have uploaded a workbook, maybe it gets a little bit clearer what i mean if you look in that.


Masun

[Trebor76]

Hi Masun,

Let us know how this goes:

Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    'Display all entries from Col's H and I where there is the text 'Missing' in Col Z of the same Row.

    Dim rngCell As Range
    Dim strMyMessage As String
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("Z2:Z" & Range("Z" & Rows.Count).End(xlUp).Row)
        If InStr(rngCell, "Missing") > 0 Then
            If strMyMessage = "" Then
                strMyMessage = "Something wrong with:" & vbNewLine & Range("H" & rngCell.Row) & " " & Range("I" & rngCell.Row)
            Else
                strMyMessage = strMyMessage & vbNewLine & Range("H" & rngCell.Row) & " " & Range("I" & rngCell.Row)
            End If
        End If
    Next rngCell
    
    If strMyMessage = "" Then
        MsgBox "No instances of ""Missing"" were found in Col. Z.", vbInformation, "Excel Guru"
    Else
        MsgBox strMyMessage, vbExclamation, "Excel Guru"
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

[Masun]

Hi Trebor,

Thanks for your help! Seems to be working fine!

/Masun

[Trebor76]

You're welcome

If you could please mark the thread as solved it would be appreciated.

Regards,

Robert

[Masun]

Trebor, thanks for your help but unfortunately i did not test the code enough and i would really appreciate som further help from you (or anyone else for that matter).

I use the code you provided as a part of another code. If there are any "Missings" in col Z the msgbox appears just like i want to but when I click the ok buttun the code continues to run, i would like the msgbox to appear and when clicking the ok button i want to stop running the code, some sort of end sub if you understand what i mean. If there are no missings i dont need a msgbox to appear, then the code can keep on running like Forrest Gump...

/Masun

[Trebor76]

Hi Masun,

Replace these lines...

If strMyMessage = "" Then
        MsgBox "No instances of ""Missing"" were found in Col. Z.", vbInformation, "Excel Guru"
    Else
        MsgBox strMyMessage, vbExclamation, "Excel Guru"
    End If

...with this:

If strMyMessage <> "" Then
        MsgBox strMyMessage, vbExclamation, "Excel Guru"
        Exit Sub
    End If

HTH

Robert

[Masun]

Trebor,

Perfect! Thank you!! Once again i will mark this thread as solved...

/Masun