Excel VBA Macro count values

[euroride7]

Can somebody please tell me how to write the VBA code

for this. Thank you.

The following is a security log indicating a bunch of

differents events that occured (sample data). The

actual list is much longer. I have 4 workseets

corresponding to 4 different computer logs.

Event ID Category
529 Logon/Logoff
537 Logon/Logoff
681 Account Logon
565 Directory Service Access
627 Account Management
677 Account Logon

I need to create a macro that counts how many times

each Event ID occured and put it in a new column

"Instances" to look like below. Please help.

Event ID Category Instances
529 Logon/Logoff 4
537 Logon/Logoff 78
539 Logon/Logoff 17
565 Directory Service Access 590
577 Privilege Use 1
627 Account Management 1
675 Account Logon 1852
676 Account Logon 90
677 Account Logon 121
681 Account Logon 41

 

[Roger Govier]

Hi

You don't need any code. Use a Pivot Table.
Place your cursor in any cell within the table >Data>Pivot Table>Finish
On the PT skeleton that appears on the new page
Drag Event ID to the Row area
Drag Category to the Row area
Drag Category again, to the Data area

Double click on Event ID>Subtotals>None

 

[Don Guillett]

Sub countunique()
dc = "e" 'destination column for list
slr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:B" & slr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Cells(1, dc), Unique:=True

For i = 2 To Cells(Rows.Count, dc).End(xlUp).Row
Cells(i, dc).Offset(, 2) = _
WorksheetFunction.CountIf(Range("a2:a22"), Cells(i, dc))
Next i

End Sub

 

[ShaneDevenshire]

Hi,

Here is another non-code solution:

=COUNTIF(A$1:A$1000,D1)

This assumes your Event ID's are in column A, and you enter a list of uinque
Event ID's in column D starting in D1. Just copy the formula down.