Using ActiveCell.FormulaR1C1 = and =COUNTIF

Q

QLD_Mark

Joined
Nov 12, 2008
Messages
2
Reaction score
0
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??
 
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
 
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
 
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....:thumb: :thumb:
 
Back
Top