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! :)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
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!! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

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