Macro code for Count If function

[sanz]

Hi All,

I have a macro recorded for the counif function, but the range selected is for static (ie B2:B8), i want this macro to be tweaked for dynamic active range selection(ie B2 till last active cell).

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+t
'
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:R[6]C[1],RC[1])"
Range("B3").Select
Selection.End(xlDown).Select
Range("A8").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Range("A4").Select
End Sub


Any help would be appreciated

Tried to source and assemble the code, but this doesn't seem to work. Here the range value is static which need to be added by one untill last cell

Sub foo()

Dim lastRowColumnB As Long, var2 As String
lastRowColumnB = Range("B65000").End(xlUp).Row
var2 = Range("B2").Value

For i = 2 To lastRowColumnB
Cells(i, 1) = Application.CountIf(Range("B$2:B" & Cells(Rows.Count, 2).End(xlUp).Row), var2)
Next
End Sub



Regards,
Sanz

 

[Michael M]

Don't have excel at the moment....but

Sub Macro4()
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("A2:A" & lr).Formula = "=COUNTIF($B$2:B" & lr & ",B2)"
End Sub

 

[MrBillBenson]

Sanz, you used the terms "Selection" and "last active cell". Are you really talking about a selected range rather than all rows on the sheet? If so, that would be in conflict with the fact that you want the formula in the active cell. In which case you would want to have the code run in stages:

Dim RngInput as Range
Dim rngCellsLike as Range
Set RngInput = Application.InputBox("Select the cells to count:","Range Selection",,,,,,8)
Set RngCellsLike = Application.InputBox("Which Cell to Compare To:","Cell Selection",,,,,,8)

ActiveCell.Formula = "=COUNTIF(" & RngInput.Address &"," & RngCellsLike.Address &")"