Message box to return values in cells

[Godders199]

Hello, i have been looking for an example of VBA for a message box which returns values of cells where criteria is met. While i have found VBA which will return the cell range, i cannot find an example of displaying values.

here is an example of what i mean.

[TABLE="width: 331"]
<tbody>[TR]
[TD]order[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Pete[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bill[/TD]
[TD]13/11/2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]james[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]jim [/TD]
[TD]13/11/2017[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

From the above table, I would require a message box to display the order number and name if the date is blank.

If anyone has an example of this VBA it would be appreciated.

Thanks

 

[My Aswer Is This]

Try this:

Sub Check_Date()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim ss As String
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If Cells(i, "C").Value = "" Then ans = Cells(i, 1).Value & vbTab & Cells(i, 2).Value & vbNewLine & ans
        
    Next
ss = "These Dates Are Blank" & vbNewLine & "Order" & vbTab & "Name" & vbNewLine
MsgBox ss & ans
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Something like

Sub MsgList()

    Dim Cl As Range
    Dim Rng As Range
    Dim Msg As String
    
    For Each Rng In Columns(3).SpecialCells(xlBlanks).Areas
        If Rng.Count = 1 Then
            Msg = Msg & Rng.Offset(, -2) & " " & Rng.Offset(, -1) & vbLf
        Else
            For Each Cl In Rng
                Msg = Msg & Cl.Offset(, -2) & " " & Cl.Offset(, -1) & vbLf
            Next Cl
        End If
    Next Rng
    MsgBox Msg
    
End Sub

 

[Godders199]

thanks for your help

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

thanks for your help

 

[Fluff]

thanks for your help

As a matter of interested, which of us were you talking to, or both?

 

[Godders199]

Once again thank , you, what do i need to add to the code, so it only displays the message box when there are rows that meet the criteria?

Thanks

 

[Fluff]

Try this

Sub MsgList()

    Dim Cl As Range
    Dim Rng As Range
    Dim Msg As String
    
    For Each Rng In Columns(3).SpecialCells(xlBlanks).Areas
        If Rng.Count = 1 Then
            Msg = Msg & Rng.Offset(, -2) & " " & Rng.Offset(, -1) & vbLf
        Else
            For Each Cl In Rng
                Msg = Msg & Cl.Offset(, -2) & " " & Cl.Offset(, -1) & vbLf
            Next Cl
        End If
    Next Rng
    If Len(Msg) > 0 Then MsgBox Msg
    
End Sub

 

[My Aswer Is This]

My script should do exactly what you asked for.
But it's true if no rows meet the criteria you would get a message showing no value.
Try this:
If no rows meet your criteria you will get no message::

Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim ss As String
Dim Lastrow As Long
Dim x As Long
x = 1
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If Cells(i, "C").Value = "" Then ans = Cells(i, 1).Value & vbTab & Cells(i, 2).Value & vbNewLine & ans: x = x + 1
        
    Next
ss = "These Dates Are Blank" & vbNewLine & "Order" & vbTab & "Name" & vbNewLine
 If x > 1 Then MsgBox ss & ans
Application.ScreenUpdating = True
End Sub

 

[Godders199]

My Aswer is this - Thank you