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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,899
Office Version
  1. 365
Platform
  1. Windows
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

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

ellen0804

New Member
Joined
Jan 12, 2011
Messages
10
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! :)
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

What is your code for the average?
 

ellen0804

New Member
Joined
Jan 12, 2011
Messages
10
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 & ")"
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Make sure that the destination cells are formatted as General then try again.
 

ellen0804

New Member
Joined
Jan 12, 2011
Messages
10
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!! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,277
Messages
5,836,343
Members
430,421
Latest member
Natas

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
Top