Countif in VBA Module

[475CDY]

Hi,
I have a column of just under 62,000 rows where I am trying to get an accurate count for each value on each row. If I type =COUNTIF($J$2:$J$61948,J2) in cell K2 and copy it down, then it works correctly. However, I want to be able to run it using VBA, but it keeps blowing up on me. Here is the module that I am using:



Sub PrimaryCount()
Dim LastRow as long
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
Range("K2:K" & LastRow).Value = "=COUNTIF($J$2:$J$& LastRow,J2)" <- 1004 error
End Sub


When I try to run the code I get a Run-time error ‘1004’: Application-defined or object-defined error. After I get the error, if I highlight the row I get a “<type mismatch>” on what appears to be the second "LastRow" underlined above. What do I need to fix to make this work using VBA – or – is there a better option?

Mark

[JKwan]

see if this fixes your problem

Range("K2:K" & LastRow).Formula = "=COUNTIF($J$2:$J$" & LastRow & ",J2)"

[p45cal]

try:
Sub PrimaryCount()
Dim LastRow As Long
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
With Range("K2:K" & LastRow)
.FormulaR1C1 = "=COUNTIF(R2C10:R" & LastRow & "C10,RC[-1])"
.Value = .Value 'convert to plain values
End With
End Sub

or:
Sub PrimaryCount2()
Dim LastRow As Long
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
Range("K2:K" & LastRow).Value = Evaluate("COUNTIF(J2:J" & LastRow & ",J2:J" & LastRow & ")")
End Sub

[475CDY]

That did it!
Thank you, so much for your help - I really appreciate it!
Mark

[475CDY]

Thank you, p45cal! Both of your ides worked, too. I appreciate your help.
Mark