Show names in message box.

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
170
Hi everyone,

I am using vba to count the number of cells in a worksheet that contain "0" and the number of cells that contain a value then display the results in a msgbox, this code is working fine... what i would like it to do if possible is to also show in the same message box the members names that = "0".

Members names are in column "B", and the "0" value is in column "I", below is my code so far that works..

hope this is easy to understand..

Code:
Sub Count_Fees()
Dim count As Integer
Dim count1 As Integer
Worksheets("Fees Paid").Select
Dim LR As Long, i As Long
count = 0
LR = Range("I" & Rows.count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("I" & i).Value = "0" Then
    count = count + 1
    
    End If
Next i
Dim L As Long, j As Long
count1 = 0
LT = Range("I" & Rows.count).End(xlUp).Row
For j = LT To 2 Step -1
   If Not Range("I" & j).Value = "0" Then
    count1 = count1 + 1
    
   End If
Next j
Worksheets("Fees Paid").Select
MsgBox count1 & " Fees Paid Counted and  " _
& count & " Fees Unpaid Counted"
 
End Sub

Thanks in advance
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Maybe this

Code:
Sub Count_Fees()
Dim count As Integer
Dim count1 As Integer
Worksheets("Fees Paid").Select
Dim LR As Long, i As Long
count = 0
LR = Range("I" & Rows.count).End(xlUp).Row
n = ""
For i = LR To 2 Step -1
    If Range("I" & i).Value = "0" Then
    count = count + 1
    n = Range("B" & i).Value & ", " & n
    End If
Next i
Dim L As Long, j As Long
count1 = 0
LT = Range("I" & Rows.count).End(xlUp).Row
For j = LT To 2 Step -1
   If Not Range("I" & j).Value = "0" Then
    count1 = count1 + 1
   End If
Next j
Worksheets("Fees Paid").Select
MsgBox count1 & " Fees Paid Counted and  " _
& count & " Fees Unpaid Counted"
MsgBox n & " Fees are Unpaid"
 
End Sub
 
Last edited:
Upvote 0
Hi everyone,

I am using vba to count the number of cells in a worksheet that contain "0" and the number of cells that contain a value then display the results in a msgbox, this code is working fine... what i would like it to do if possible is to also show in the same message box the members names that = "0".

Members names are in column "B", and the "0" value is in column "I", below is my code so far that works..

hope this is easy to understand..

Thanks in advance

Code:
Sub Count_Fees()

Dim count As Long, count1 As Long, WS As Worksheet, TS As String, Sh_A As Variant

Set WS = Worksheets("Fees Paid")

Dim LR As Long, i As Long

With WS

    Sh_A = .UsedRange.Value2

    LR = .Range("I" & Rows.count).End(xlUp).Row

    For i = LR To 2 Step -1
        
        If Sh_A(i, 9).Value2 = 0 Then 'if the value in column I = 0
            TS = Sh_A(i, 2) & vbNewLine & TS 'column B value + new line + previous string
            count = count + 1
        Else
            count1 = count1 + 1
        End If
        
    Next i

End With

Worksheets("Fees Paid").Activate

MsgBox count1 & " Fees Paid Counted and " & count & " Fees Unpaid Counted" & _
vbNewLine & vbNewLine & "Members pending payment" & vbNewLine & vbNewLine & TS
 
End Sub
 
Upvote 0
Thanks for your reply MoshiM,

I tried your code and it debugs at the line,

Code:
Sh_A = .UsedRange.Value2
 
Upvote 0
Thanks for your reply MoshiM,

I tried your code and it debugs at the line,

Code:
Sh_A = .UsedRange.Value2

Did you point out the correct line? I've corrected the error in the if statement but other than that it should work properly.

Code:
Sub Count_Fees()

Dim count As Long, count1 As Long, WS As Worksheet, TS As String, Sh_A As Variant

Set WS = Worksheets("Fees Paid")

Dim LR As Long, i As Long

With WS

Sh_A = .UsedRange.Value2
LR = .Range("I" & Rows.count).End(xlUp).Row

    For i = LR To 2 Step -1
        
        If Sh_A(i, 9) = 0 And Not IsEmpty(Sh_A(i, 9)) Then 'if the value in column I = 0
            TS = Sh_A(i, 2) & vbNewLine & TS 'column B value + new line + previous string
            count = count + 1
        ElseIf Sh_A(i, 9) <> 0 And Not IsEmpty(Sh_A(i, 9)) Then
            count1 = count1 + 1
        End If
        
    Next i

End With

Worksheets("Fees Paid").Activate

MsgBox count1 & " Fees Paid Counted and " & count & " Fees Unpaid Counted" & _
vbNewLine & vbNewLine & "Members pending payment" & vbNewLine & vbNewLine & TS
 
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top