Countif in VBA

[joycesolomon]

Hi

I am trying to put the below macro recorded code into a loop since i have about 200 rows that needs this


ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[65534]C[-2],RC[-1])"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C[-2]:R[65533]C[-2],RC[-1])"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2]C[-2]:R[65532]C[-2],RC[-1])"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-3]C[-2]:R[65531]C[-2],RC[-1])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-4]C[-2]:R[65530]C[-2],RC[-1])"
Range("E7").Select


Can someone please help. What i am trying to achieve is I have column C that has dates that has duplicates, i have sorted it without duplicate in column D. I want to know how many time(count) each date in column D has duplicate, which is the loop i want for column E. Can someone please help...i am so not good in loops and VBA

 

[Jonmo1]

Welcome to the board..

Try

Sub CountDupes()
Dim lrC As Long, lrD As Long
lrC = Cells(Rows.Count, "C").End(xlup).Row
lrD = Cells(Rows.Count, "D").End(xlup).Row
Range("E2:E" & lrD).Formula = "=COUNTIF($C$2:$C$" & lrC & ",$D2)"
End Sub

Hope that helps.

 

[joycesolomon]

You are a genius Jonmo1...thanks a zillion, it worked!!!!

When you have some time, mind explaining or teaching me the code below, so i can understand it better...thanks again

 

[Jonmo1]

Glad to help, thanks for the feedback...

These 2 lines
lrC = Cells(Rows.Count, "C").End(xlup).Row
lrD = Cells(Rows.Count, "D").End(xlup).Row

Create variables that reflect the Row # of the last occupied row in their column (C and D)

This uses that varible to define a range from Row2 to lrD in column E
Range("E2:E" & lrD).
Same in the countif formula.


That's all pretty common stuff, and very usefull to put in your bag of tricks.


Now the real fun part is how it put the formula in all the cells without a loop.

As an exercise to see how it works..
Go to a blank sheet and highlight a range of cells, say D1:E10
Now, type =A1 - do not press enter
instead, press CTRL + ENTER
You can see that the formula got entered in all 10 cells with 1 stroke.
And the reference incrimented accordingly A1, A2, A3 etc..

If lrD = 500, then This part
Range("E2:E" & lrD).Formula is saying it will enter the formula into E2:E500
Just like you did pressing CTRL + ENTER.

Now, The formula in VBA is this
"=COUNTIF($C$2:$C$" & lrC & ",$D2)"

If lrC = 200, then it is
"=COUNTIF($C$2:$C$200,$D2)"

The $'s are used to lock in the reference for either Column or Row.
As it's entered in subsequent rows, the $C$2:$C$200 does not change,
But $D2 changes to $D3 - $D4 etc..


Hope that helps.

 

[joycesolomon]

thank you for the tricks and awesome explanation!!!!