MSGBOX Union

[gerald24]

Hi Guys,

I am trying to write a code that will prompt me about missing fields.

For example I have 2 columns. What I want the message box to show is that "Rudy","Jerry" and "Tom" age is missing. Rudy is only shown once and not should be displayed as " Jerry & Rudy & Rudy & Tom have missing age. I tried to write my code as it would show msgbox per name with missingage, but It did not work and I realized it would be better for the prompt to show in one message box.

---

Column J Column K
Name Age
Gio 15
Jerry (blank)
Rudy (blank)
Rudy (blank)
Tom (blank)
Bob 20

here's my code

Sub TEST()
Dim MissingAge As Range
Dim Name As Range
Dim Name2 As Range
Dim LastRow As Integer

LastRow = Cells(Rows.Count, "J").End(xlUp).Row

For Each MissingAge In Range("K2", Range("K" & LastRow).End(xlUp))
            If IsEmpty(MissingAge) = False Then 'Do Nothin
                Else
                If Name2 Is Nothing Then
                    Set Name= MissingAge.Offset(0, -1)
                    MsgBox "Do you want to proceed?", vbYesNo + vbQuestion, Name+ " has missing Age"
                        If vbYes Then
                            Set Name2= Name
                            'Do Nothing
                        Else
                            Exit Sub
                        End If 'End If vbYes
                Else
                    If Name= Name2Then
                    'Do Nothing
                         Else
                         MsgBox "Do you want to proceed?", vbYesNo + vbQuestion, Name+ " has missing Age"
                            If vbYes Then
                                Set Name2= Name
                                'Do Nothing
                            Else
                                Exit Sub
                            End If
                    End If 
                End If 
            End If 
         
    Next MissingAge

End Sub

I would really appreciate if someone can check it or even write a new code all over as I think my codes are messed up. Thanks!!!

 

[JLGWhiz]

Try this

Sub t()
Dim c As Range, Nm As String
    With ActiveSheet
        For Each c In .Range("K2", .Cells(Rows.Count, 10).End(xlUp).Offset(, 1))
            If c = "" Then
                If Nm = "" Then
                    Nm = c.Offset(, -1)
                ElseIf InStr(Nm, c.Offset(, -1).Value) = 0 Then
                    Nm = Nm & ", " & c.Offset(, -1)
                End If
            End If
        Next
        MsgBox Nm & " are missing age."
    End With
End Sub

 

[gerald24]

Thanks Sir!!! works like how a desired.

 

[Peter_SSs]

Thanks Sir!!! works like how a desired.

I'm not so sure.
For the data below, that code only reports Jerry, Rudyard & Tomas as missing.

Book1
	J	K
1	Name	Age
2	Gio	15
3	Jerry
4	Rudyard
5	Rudy
6	Tomas
7	Bob	20
8	Tom
Missing Ages

I would suggest this modification

<del>ElseIf InStr(Nm, c.Offset(, -1).Value) = 0 Then</del>
ElseIf InStr(", " & Nm & ",", ", " & c.Offset(, -1).Value & ",") = 0 Then

 

[JLGWhiz]

Glad to help,
Regards, JLG

 

[JLGWhiz]

I'm not so sure.
For the data below, that code only reports Jerry, Rudyard & Tomas as missing.

	J	K
Name	Age
Gio
Jerry
Rudyard
Rudy
Tomas
Bob
Tom

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Missing Ages​

I would suggest this modification

<del>ElseIf InStr(Nm, c.Offset(, -1).Value) = 0 Then</del>
ElseIf InStr(", " & Nm & ",", ", " & c.Offset(, -1).Value & ",") = 0 Then

You are right, Peter, if only short one word names are used, but I suspect that the names actually used will avert the fallacy of the InStr comparisons.
Regards, JLG

 

[Peter_SSs]

You are right, Peter, if only short one word names are used, but I suspect that the names actually used will avert the fallacy of the InStr comparisons.
Regards, JLG

Possibly, but even with full names you could still get the problem with, for example ..
David Williamson
David Williams
The suggested modification should remove any possibility of this sort of thing.

 

[Fazza]

another way

Sub usage()

    MsgBox MissingValues(FirstColumn:="J")


End Sub


Function MissingValues(ByVal FirstColumn As String) As String
    'input one column, return string with list of distinct entries from that column that are blank in the adjacent cell/column
    
    'https://www.mrexcel.com/forum/excel-questions/1064292-msgbox-union.html
    
    Dim i As Long
    Dim ar As Variant
    Dim dic As Object
    
    ar = Range(Cells(1, FirstColumn), Cells(Rows.Count, FirstColumn).End(xlUp).Offset(, 1)).Value2
        
    Set dic = CreateObject("Scripting.Dictionary")
    
    For i = LBound(ar, 1) + 1 To UBound(ar, 1)
        If Len(ar(i, 2)) = 0 Then
            If Not dic.exists(ar(i, 1)) Then dic.Add ar(i, 1), Nothing
        End If
    Next i


    If dic.Count = 0 Then
        MissingValues = "No missing entries for " & ar(1, 2) & " field."
    Else
        MissingValues = ar(1, 2) & " entry missing for " & ar(1, 1) & " field value" & IIf(dic.Count = 1, ":", "s:") & vbCr & Join$(dic.keys, vbCr)
    End If
    Erase ar
    Set dic = Nothing

End Function

 

[gerald24]

Sorry, was not able to get back to this. I will try to run thru all of your inputs.

thanks guys!!!