Countif in VBA showing error in downward motion..

[srlakra]

Hi,

I have different values in "A" Column duplicates also. I have to get the count of value in A2 from A2:A100 in B2, then count of value in A3 from A3:A100 in B3,,,
count of value in A20 from A20:A100 in B20 and so on.

I worked on it with my code but some error is there I can't find.

Please help....

Dim i As Long
Dim lastusedrow As Long

lastusedrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastusedrow
cells(I,"B")=application.WorksheetFunction.CountIf(Range("A"&i&":A"& lastusedrow),"A"&i)

Next I

Thanks & regards,
Satish R Lakra

 

[steve the fish]

You could try:

Cells(i, "B") = WorksheetFunction.CountIf(Range(Cells(i, "A"), Cells(lastusedrow, "A")), Cells(i, "A"))

 

[JackDanIce]

Try:

Sub Macro1()
    
    Dim x       As Long
    Dim strfrm  As String
    
    strfrm = "=COUNTIF(A2:$A$@1,A2)"
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    With ActiveSheet
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        With .Cells(2, 2).Resize(x - 1)
            .Formula = Replace(strfrm, "@1", x)
            ActiveSheet.Calculate
            .Value = .Value
        End With
    End With
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

 

[srlakra]

Thanks Steve

 

[srlakra]

Thanks Jack