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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,283
Office Version
2013
Platform
Windows
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:

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
290
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
 

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
170
Thanks for your Reply Michael M

i tried your code and it does work

Thankyou
 

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
170
Thanks for your reply MoshiM,

I tried your code and it debugs at the line,

Code:
Sh_A = .UsedRange.Value2
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
290
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:

Forum statistics

Threads
1,084,753
Messages
5,379,669
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

Top