VBA to display count in a message box. xl 2003

[joe.muckle]

Hi Guru's

Does any one know how to get excel to diplay a count for certain values in a message box??

Ideally I would want the message box to contain two answers (two seperate counts).

Thanks in advance.
Joe.

 

[VoG]

Try like this

Sub test()
Dim x As Long, y As Long
x = 4
y = 1000
MsgBox "X =" & vbTab & x & vbNewLine & "Y =" & vbTab & y
End Sub

 

[RoryA]

Not much to go on, but basically:

Msgbox "Count one: " & count1 & vbcrlf & "Count two: " & count2

 

[JamesW]

Hi,

Assuming your values are numerical try this UDF:

Function countVals(valRng As Range, valCount As Integer)
    Dim cell As Object
    Dim valResult As Integer
    
    For Each cell In valRng
        If cell.Value = valCount Then valResult = valResult + 1
    Next
    
    countVals = valResult
    MsgBox countVals
End Function

Then in your sheet type:

=countVals(F1:F25,2) - Count how many 2's are in the range F1 to F25.

 

[joe.muckle]

Sorry if I was a bit vague, I have a column which has two answers (say x & y) what I want is VBA to count the occurances of each answer and then display them in a message box.

Joe.

 

[joe.muckle]

Sorry, I was meant to add that the values I want to count are actually answers to formulas.

Does this make a difference??

Thanks again.

 

[joe.muckle]

Solved this with:

Sub count()
Dim count As Integer
Dim count1 As Integer
Worksheets("Non - Workflow").Select
Dim LR As Long, i As Long
count = 0
LR = Range("A" & Rows.count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("O" & i).Value = "Not RTV" Then
    count = count + 1
    
    End If
Next i
Dim L As Long, j As Long
count1 = 0
LT = Range("A" & Rows.count).End(xlUp).Row
For j = LT To 2 Step -1
   If Not Range("O" & j).Value = "Not RTV" Then
    count1 = count1 + 1
    
   End If
Next j
Worksheets("Home").Select
MsgBox count1 & " RTV task(s) counted and  " _
& count & " Non RTV task(s) counted"
 
End Sub