Summing in VBA - Still Not Working!!!

[kwaring]

Range("A5").Select
ActiveCell.FormulaR1C1 = SUM(A1:A4)

Trying to do something like this but the compiler doesn't like the syntax... any suggestions?
Thanks,
Kyle

[ This Message was edited by: kwaring on 2002-03-24 17:13 ]

[Jay Petrulis]

On 2002-03-24 15:25, kwaring wrote:
Range("A5").Select
ActiveCell.FormulaR1C1 = SUM(A1:A4)

Trying to do something like this but the compiler doesn't like the syntax... any suggestions?
Thanks,
Kyle

Hi Kyle,

Be sure to enclose the formula to load to the worksheet in quotes. Also, no need to select unles required, as it makes the code inefficient.

Range("A5") = "=SUM(A1:A4)"

would work for you.

Bye,
Jay

[dk]

Try

ActiveCell.Formula = "=Sum(A1:A4)"

FormulaR1C1 expects a formula in relative reference format e.g. =Sum(r2c1,r3c4). For both you need to assign a string as the formula you want to use.

HTH,
D

[kwaring]

Thanks Jay and dk, however, I am still having problems... Your code works on the simple examples above and I thought I could incorporate that into my more-complex code but it still doesn't work.

Range("E" & Search) = "=SUM("E" & Start & ":" & "E" & Search)"

Where Search and Start are variables defined in VBA. When I try to run the macro, I get a Run-time error '1004': Application-defined or object-defined error
Thanks,
Kyle

[kwaring]

Actually looks like this:

Range("E" & Search + 1) = "=SUM("E" & Start & ":" & "E" & Search)"

I've tried:
Range("E" & Search + 1) = "=SUM('E' & Start & ':' & 'E' & Search)"

Still no luck

[Dave Hawley]

Hi Kwaring

Try

Range("E" & Search + 1) = "=SUM(E" & Start & ":" & "E" & Search & ")"

[Yogi Anand]

On 2002-03-24 15:25, kwaring wrote:
Range("A5").Select
ActiveCell.FormulaR1C1 = SUM(A1:A4)

Trying to do something like this but the compiler doesn't like the syntax... any suggestions?
Thanks,
Kyle

[ This Message was edited by: kwaring on 2002-03-24 17:13 ]

Hey Kyle:
Referring to the formula in your original posting, change it to:

Sub RangeAdd2()
Range("A5").Select
ActiveCell.Formula = "=sum(a1:a4)"
End Sub

Put the above sub in a module of your workbook. It works. The suggestions by Jay and dk given to you earlier do work.
So please check:
1) Install the SUB in a module of the Active Workbook
2) Check the SUB for editorial/syntax issues
3) Run the SUB

After you do the above post back what you get, VBA GURUs like Jay, dk, and Dave will be able to look at your SUB and point out where is what wrong.

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 15:17 ]

[kwaring]

That works perfectly, Dave... THANK YOU!

[Yogi Anand]

On 2002-03-24 16:45, kwaring wrote:
Thanks Jay and dk, however, I am still having problems... Your code works on the simple examples above and I thought I could incorporate that into my more-complex code but it still doesn't work.

Range("E" & Search) = "=SUM("E" & Start & ":" & "E" & Search)"

Where Search and Start are variables defined in VBA. When I try to run the macro, I get a Run-time error '1004': Application-defined or object-defined error
Thanks,
Kyle

Hi Kyle:
Sorry about my last belated post. In reference to the formula, try the following formula:

="Range(""E"&search+1&""")=""=sum(E"&start&":E"&search&")"""

results in
Range("E5")="=sum(E1:E4)"

HTH

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 15:18 ]