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

Sushiboy

New Member
Joined
Jan 18, 2019
Messages
24
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...….
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Sushiboy

New Member
Joined
Jan 18, 2019
Messages
24
regards to the previous maiil, there should be Column A, Column B and the other column is active (not fixed)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,111
.
Try this macro :

Code:
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.
 

Forum statistics

Threads
1,136,318
Messages
5,675,057
Members
419,547
Latest member
Altamash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top