I've got a list of data that i frequently refer to.
What i want to do is to have a macro that will do a countif function
and then show the results in a message box:

Apples = 5
pears = 2
oranges = 4

thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=550918

Why does this need to be in a message box - wouldn't a pivot table work
better? If you do want it in a message box, do you want to see ALL
results at once, or just selected items (see my reply at allexperts.com
for the selected item)

at it's simples
msgbox worksheetfunction.countif(Range(RangeToCheck),Data ToCheck)

replacing rangetocheck and datatocheck will give you a single message
box

Putting this in a loop, and perhaps storing the results in a string
then displaying that string would give you multiple iterations.


M3Cobb wrote:
I've got a list of data that i frequently refer to.
What i want to do is to have a macro that will do a countif function
and then show the results in a message box:

Apples = 5
pears = 2
oranges = 4

thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=550918

Hi M3Cobb.

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim myCol As Collection
Dim Arr() As Variant
Dim rCell As Range
Dim rng As Range
Dim i As Long
Dim iLRow As Long
Dim msg As String

Set WB = ThisWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

iLRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = SH.Range("A2:A" & iLRow)

Set myCol = New Collection

For Each rCell In rng.Cells
If Not IsEmpty(rCell.Value) Then
On Error Resume Next
myCol.Add rCell.Value, CStr(rCell.Value)
On Error GoTo 0
End If
Next rCell
On Error Resume Next

ReDim Arr(1 To myCol.Count, 1 To 2)

For i = LBound(Arr, 1) To UBound(Arr, 1)
Arr(i, 1) = myCol.Item(i)
Arr(i, 2) = Application.WorksheetFunction.CountIf(rng, Arr(i, 1))
Next i

For i = LBound(Arr, 1) To UBound(Arr, 1)
msg = msg & Arr(i, 1) & vbTab & Arr(i, 2) & vbNewLine
Next i
On Error GoTo 0
MsgBox msg, , "Unique Values"
End Sub
'<<=============


---
Regards,
Norman



"M3Cobb" wrote in
message ...

I've got a list of data that i frequently refer to.
What i want to do is to have a macro that will do a countif function
and then show the results in a message box:

Apples = 5
pears = 2
oranges = 4

thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile:
http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=550918

Hi M3Cobb

This should do the trick:

Public Sub MyCountIf()
ReDim strNames(0) As String
ReDim intCount(0) As Integer
Dim lngRowLoop As Long
Dim lngRowMax As Long
Dim lngColumn As Long
Dim intFoundLoop As Integer
Dim intFoundMax As Integer
Dim blnNewName As Boolean
Dim strMessage As String

lngColumn = ActiveCell.Column
intFoundMax = -1
For lngRowLoop = ActiveCell.Row To ActiveSheet.UsedRange.Rows.Count
blnNewName = True
If intFoundMax -1 Then
For intFoundLoop = 0 To intFoundMax
If StrComp(strNames(intFoundLoop), Cells(lngRowLoop,
lngColumn).Value, vbTextCompare) = 0 Then
blnNewName = False
intCount(intFoundLoop) = intCount(intFoundLoop) + 1
Exit For
End If
Next
End If
If blnNewName Then
intFoundMax = intFoundMax + 1
If intFoundMax 0 Then
ReDim Preserve strNames(intFoundMax) As String
ReDim Preserve intCount(intFoundMax) As Integer
End If
strNames(intFoundMax) = Cells(lngRowLoop, lngColumn).Value
intCount(intFoundMax) = 1
End If

Next

For intFoundLoop = 0 To intFoundMax
strMessage = strMessage & strNames(intFoundLoop) & vbTab &
intCount(intFoundLoop) & vbNewLine
Next
MsgBox strMessage, vbOKOnly, "My Countif"
End Sub

Select the first cell of the values you want to count and start this
macro

HTH,

Executor

M3Cobb wrote:
I've got a list of data that i frequently refer to.
What i want to do is to have a macro that will do a countif function
and then show the results in a message box:

Apples = 5
pears = 2
oranges = 4

thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=550918

Norman,

exaclty what i wanted! Cheers

--
M3Cob
-----------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...fo&userid=2498
View this thread: http://www.excelforum.com/showthread.php?threadid=55091