Show names in message box.

[Gregm66]

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

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]

Maybe this

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

 

[MoshiM]

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

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]

Thanks for your Reply Michael M

i tried your code and it does work

Thankyou

 

[Gregm66]

Thanks for your reply MoshiM,

I tried your code and it debugs at the line,

Sh_A = .UsedRange.Value2

 

[MoshiM]

Thanks for your reply MoshiM,

I tried your code and it debugs at the line,

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.

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