countif VBA

[awsumchillicrab]

Hi all,

I have a long list of data in column A, many of which repeat themselves.
I want column B to do a countif of the respective entry in column A.

So if "Jacky" appears 3 times in cells A12, A20, and A24,
I should have "3" showing up in B12, B20, and B24.

Also, column A does not always have the same number of rows. I have to use a LastRow coding of some sort which doesn't let me explicitly express the range I want evaluated like A1:A100.

Thank you in advance!

 

[VoG]

Why do you need VBA? In B1 and copy down

=COUNTIF(A:A,A1)

 

[awsumchillicrab]

because it's part of a bigger macro.

any help appreciated.

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:B" & LR)
    .Formula = "=COUNTIF(A:A,A1)"
    .Value = .Value
End With
End Sub

 

[awsumchillicrab]

Thanks VoG! That works.

Is it possible to do it changing

=countif(A:A, A2)

into something like

=countif(A2:Last Row of A, A2)

Your formula works, but technically covers a bigger range than what I need.

 

[VoG]

Because of the way that Excel's smart calculation engine works I don't think that the formula would be evaluated beyond the filled range. But anyway, here goes

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .Formula = "=COUNTIF(A$2:A$" & LR & ",A2)"
    .Value = .Value
End With
End Sub

 

[awsumchillicrab]

Great!
Thank you very much!