Using ActiveCell.FormulaR1C1 = and =COUNTIF

[QLD_Mark]

Hi

I want to use the following code so that the active cell is populated with the formular =COUNTIF(M2:M" & rowCount & ", "" * "")

Sub Macro1()
Dim theRange As String
Dim rowCount As Integer
rowCount = rowCount + 10 ' Set rowcount
theRange = "M" & rowCount
Range(theRange).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(M2:M" & rowCount & ", "" * "")"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = xlAutomatic
End With
End Sub

Unfortunately I get the following result. =COUNTIF('M2':'M40', " * ")

Close, however no cigar!!! How do I get rid of the quotes around the M2 & M40??

 

[losmac]

Sub Macro1()
Dim rng As Range

'create range object
Set rng = Thisworkbook.Worksheets("Sheet1").Range("M10")

'with range
With rng
.FormulaR1C1 = "=COUNTIF(""M2:M""" & rowCount & ", "" * "")"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = xlAutomatic
End With
End With

'delete object -> free memory
Set rng = Nothing

End Sub

 

[QLD_Mark]

Hi All

I finally worked out the answer....Well an answer.

Instead of using "FormulaR1C1" I just used "Formula" and it now works fine. Thanks for the additional information about deleting the object to free up memory....

Cheers
Mark

 

[Madxgraphics]

Well there you go then...Job done..Thanks for sharing your solution with us...Always good to know what results people came out with in the end....