Inserting a formula that uses variable for range

[sagain2k]

I'm having trouble having VBA insert a formula that uses a variable to represent the desired range used in the formula. I can get it to work if I use the method of R and C numbers relative to the active cell, but for some reason it adds extra ' symbols if I try using a simple text string for the range.

The example I'm trying to get to work is to have VBA insert this formula to show the number of rows in a given range:

=Rows(a1:a4)

Here's what I tried:

Sub addformula()

Dim testrangestr, RStart, REnd, testrange
RStart = -4
REnd = -1

'Range("c5").Select 'SELECTS CELL THE FORMULA WILL BE PUT IN

'METHODS TRIED FOR RANGE STRING
'testrange = "a1" & ":" & "a4"
'testrange = "a" & 1 & ":" & "a" & 4 'still adds the extra ' symbols

testrange = "a1:a4"
testrangestr = "=" & "rows(" & testrange & ")"
MsgBox ("TestRangeStr = " & testrangestr)

ActiveCell.FormulaR1C1 = testrangestr 'THIS FOR SOME REASON PUTS
'THE STRING: =ROWS('A1':'A4') THAT HAS UNWANTED 1 SYMBOLS
'SO IT WON'T WORK AS A FORMULA.


'ALTERNATE METHOD THAT USES OFFSETS FROM ACTIVE CELL

Range("a5").Select 'SELECTS CELL TO PUT FORMULA
testrangestr = "=" & "rows(R[" & RStart & "]C:R[" & REnd & "]C)"
MsgBox ("TestRangeStr offset = " & testrangestr)
ActiveCell.FormulaR1C1 = testrangestr 'This works!

End Sub

It's strange because it appears that the first testrangestr looks fine without the unwanted ' symbols, but Excel adds them for some reason thus making the formula not work. Any ideas on what I'm doing wrong? Thanks!

--Ray

 

[sagain2k]

Whoops! A couple typos. It should be:

Dim testrangestr, RStart, REnd, testrange
RStart = -4
REnd = -1

Range("c5").Select 'SELECTS CELL THE FORMULA WILL BE PUT IN

'METHODS TRIED FOR RANGE STRING
'testrange = "a1" & ":" & "a4"
'testrange = "a" & 1 & ":" & "a" & 4 'still adds the extra ' symbols

testrange = "a1:a4"
testrangestr = "=" & "rows(" & testrange & ")"
MsgBox ("TestRangeStr = " & testrangestr)

ActiveCell.FormulaR1C1 = testrangestr 'THIS FOR SOME REASON PUTS
'THE STRING: =ROWS('A1':'A4') THAT HAS UNWANTED ' SYMBOLS
'SO IT WON'T WORK AS A FORMULA.

 

[Chas17]

Range("C5").Select
ActiveCell.Formula = "=rows(a1:a4)"

Chas

To use your variable

testrange = "=rows(a1:a4)"
Range("C5").Select
ActiveCell.Formula = testrange


use formula vice formulaR1C1