vba messagebox return a string of data associated with other column Cells

[Sushiboy]

Hi, Trying to amend the code to include in the message box details of failed items.

So in the active column, I have some cells indicating "FAILED". in the adjacent columns I have data in Column A and B. How will the below code be amended so it lists the corresponding data in Column A and B when the message box appears??

At the moment it indicates how many failed items in the active column, but I need to add to this and provide details what the failed items are from Column A and B.
Many thanks


Sub Messagebox1() Dim instances As Long instances = WorksheetFunction.CountIf(Columns(ActiveCell.Column), "FAILED") MsgBox "Found " & instances & " Failed Upload(s)", vbInformation, "TITLE"
End sub​

Column A Column B Column (Active column)
B125ASD Fund x Failed
B85454A fund Y Failed
C154466 Fund Z Failed
D812324 Fund D Pass...….

 

[Sushiboy]

regards to the previous maiil, there should be Column A, Column B and the other column is active (not fixed)

 

[Logit]

.
Try this macro :

Option Explicit


Sub mesage()
    Dim rng As Range
    Dim txt As String
    Dim cel As Range
    Dim str As String
    Dim row As Long
    Dim col As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      txt = ActiveWindow.RangeSelection.AddressLocal
    Else
      txt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set rng = Application.InputBox("Please select range:", "Highlight Range", txt, , , , , 8)
    If rng Is Nothing Then Exit Sub
    On Error Resume Next
    For row = 2 To rng.Rows.Count
        For col = 1 To rng.Columns.Count
            If rng.Cells(row, 2).Value = "Failed" Then
                str = str & rng.Cells(row, col).Value & vbTab
            End If
        Next
        str = str & vbCrLf
    Next
    MsgBox str, vbInformation, "Failed Funds"
End Sub

When the pop-up requests the range to scan, be certain to include the column headers in row 1.

 

[Sushiboy]

Thank you logit...will give that a try! ��

 

[Logit]

You are welcome.