Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Sep 14, 2022
Solved

vba countif formula

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!

excel
Formulas and Functions
Macros and VBA
  • Harun24HR's avatar
    Harun24HR
    Sep 14, 2022

    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
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 14, 2022

    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

Resources