Summing in VBA - Still Not Working!!!

kwaring

New Member
Joined
Mar 14, 2002
Messages
49
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :(
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top