Recorded Excel macro with countIf function with lastrow

0
votes

My problem is following. I am recording macro for for sheet that´s range is dynamic.

The formula is =CountIF(A:A;A2) and recorded it:

Range("M2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-12],RC[-12])"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M4333"), Type:=xlFillDefault
Range("M2:M4333").Select

Since the rows are dynamic, I want to end every formula to the lastrow. And I searched here for answer and copied this fuction to the beginning of the macro.

Function GetLastRow(sht As Worksheet, col As String) As Integer
GetLastRow = sht.Range(col & CStr(sht.Rows.Count)).End(xlUp).row

Now I am trying to call this to the orginal recorded macro

Range("M2").Select
Range(Selection, Selection.End(xlDown)).Select
Formula1 = "=COUNTIF(A:A" & GetLastRow(Sheets("Sheet1"), "A") & ",A2))"

I changed it looking like the formula that is copied in worksheet and not like it´s recorded. I would appreciate some help to this. It would be better if the lastrow function is used with recorded formula since I have lots other formulas to come with same problem.

1
excelmacrosvba
It's Range.Formula or Range.FormulaR1C1, not .Formula1. - user4039065

1 Answers

0
votes

You want to fill column M from row 2 down to the row in column M that meets the last populated cell in column A. Use the Range.Offset property and Range.Address property to get the correct address to use in the COUNTIF function.

With Worksheets("Sheet1")  'you should know what worksheet you are on!
    With .Range("M2:M" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        .FormulaR1C1 = "=COUNTIF(" & .Offset(0, -12).Address(ReferenceStyle:=xlR1C1) & ", RC1)"
    End With
End With

        AM_formular1c1