Countif on a new inserted column

[Ossian13]

Hi guys,

i have in column A a registration number, and i want to insert a column to add a countif formula to calculate how many times a number appears in my sheet. I had no problems in doing so but i want to know how to write the code so that the countif formula to stop at the last number in column a instead of going all the way down.

my code so far

Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIFS(A:A,A2)"
Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B2" & xLastrow)

Regards,
Ossian

 

[Joe4]

Are you trying to limit the range you are looking up into, or the number of cells you are populating with the formula?

If you wanted to limit the cells you are looking up into, it would look something like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"A").End(xlUp).Row
Range("B2").Formula = "=COUNTIFS(A2:A" & lastRow & ",A2)"

If the later, than maybe something like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"A").End(xlUp).Row
Range("B2:B" & lastRow).Formula = "=COUNTIFS(A:A,A2)"

 

[kbrummert]

Sub test()

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIFS(C[-1],RC[-1])"
    
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown


End Sub

 

[Ossian13]

Thanks both of you!!!