finding the sum and average up to the last row

ellen0804

New Member
Joined
Jan 12, 2011
Messages
10
Hi!
I'm working on a macro in which I want to find the sum, average and median of a column up to the last row. I have defined a variable to find the last row:

'Find the last row
Range("L32").Select
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row

Range("L" & LastRow).Select
ActiveCell.Offset(1, 0).Select

This last row thing works great in earlier places in the code. Now, later in the code I want to use this variable to find the sum and the mean and median of a couple columns from row 2 to the last row. Here's what I have, but it only sums the first 30-ish rows:

'Fill out the number correct, total number of trials, reaction time and solution time
Range("S7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[-2]:R[26]C[-2])"
Range("S8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-6]C[-8]:R[77]C[-8], ""C"") + COUNTIF(R[-6]C[-8]:R[77]C[-8], ""E"") + COUNTIF(R[-6]C[-8]:R[77]C[-8], ""SC"")"
Range("S9").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-7]C[-4]:R[24]C[-4])"
Range("S10").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-8]C[-4]:R[23]C[-4])"
Range("S11").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-9]C[-3]:R[22]C[-3])"
Range("S12").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-10]C[-3]:R[21]C[-3])"
Range("S13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-11]C[-5]:R[20]C[-5])"
Range("S14").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-12]C[-5]:R[19]C[-5])"


I've tried to do something like this (for the sum, median and average commands):

ActiveCell.FormulaR1C1 = "=SUM("R2:R"&LastRow)"

But it gives me a compile error? Am I missing something? And can I do this for sum, median and average?
Thanks so much for your help! I'm clearly still very new to writing code! :)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
For the SUM, instead of:
ActiveCell.FormulaR1C1 = "=SUM("R2:R"&LastRow)"
Try:
Code:
ActiveCell.Formula = "=SUM(R2:R" & LastRow & ")"
Also, for your LastRow, you could use this instead (saves you having to remember what the last row number is:
Code:
LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
Upvote 0
Try like this

Code:
ActiveCell.Formula = "=SUM(R2:R" & LastRow & ")"
 
Upvote 0
Wow! That was so super fast! Thanks so much! It seems to be working, however, it's giving me a #NAME? error when I run the macro. When I click on the cell this is the formula it's giving me:

=AVERAGE('P2':'P74')

And when I remove the ' ' it runs just fine. Any suggestions?

Thanks again! I'm learning so much! :)
 
Upvote 0
Oh! Sorry -- it's doing it for all of these:

'Fill out the number correct, total number of trials, reaction time and solution time
Range("T7").Select
ActiveCell.Formula = "=SUM(Q2:Q" & LastRow & ")"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(K2:K" & LastRow & ", ""C"") + COUNTIF(K2:K" & LastRow & ", ""E"") + COUNTIF(K2:K" & LastRow & ", ""SC"")"
Range("T9").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(O2:O" & LastRow & ")"
Range("T10").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(O2:O" & LastRow & ")"
Range("T11").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(P2:P" & LastRow & ")"
Range("T12").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(P2:P" & LastRow & ")"
Range("T13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(N2:N" & LastRow & ")"
Range("T14").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(N2:N" & LastRow & ")"
 
Upvote 0
Make sure that the destination cells are formatted as General then try again.
 
Upvote 0
Thanks! That didn't work, but I realized I had R1C1 at the end of the statement to the left of the equal sign. When I removed those it worked just fine. Thanks again super much for your help!! :)
 
Upvote 0

Forum statistics

Threads
1,216,172
Messages
6,129,292
Members
449,498
Latest member
Lee_ray

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