vba countif formula

[hrh_dash]

How do i correct the countif formula for using the FormulaR1C1 method?

Excel formula is as follows:
B2 to the last row = Countif(A:A,A1) and this be drag down to the last row with value

Macro as follows:

    lastRow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row
    
    Destws.Range("B2:B" & lastRow).FormulaR1C1 = "=countif(RC[-1]:lastRow, RC[-1})"

Appreciate the help in advance!

[#2 · Accepted solution]

@hrh_dash Try below sub-

Sub MyMacro()
Dim Destws As Worksheet

Set Destws = Worksheets("Sheet1")
lastrow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row
    
    Destws.Range("B2:B" & lastrow).FormulaR1C1 = "=COUNTIFS(RC[-1]:R[" & lastrow & "]C[-1],RC[-1])"
End Sub

[#3]

thanks for the assist!