finding the sum and average up to the last row

[ellen0804]

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!

 

[JackDanIce]

For the SUM, instead of:
ActiveCell.FormulaR1C1 = "=SUM("R2:R"&LastRow)"
Try:

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:

LastRow = Range("A" & Rows.Count).End(xlUp).Row

 

[VoG]

Try like this

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

 

[ellen0804]

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]

What is your code for the average?

 

[ellen0804]

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]

Make sure that the destination cells are formatted as General then try again.

 

[ellen0804]

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