How to use SUM, MAX or MIN on a variable column range

[NinaE_11]

Hello,
I'm attempting to do some simple worksheetfunctions on a column of values (# of values is likely to change and needs to be dynamic) and to place that SUM (or MAX , MIN) in an offset cell below the column. Data will always start in D7. Here's what I have so far:

'Create first and last row for formulas
lastrow = Sheets("SC").Cells(Sheets("SC").Rows.Count, "B").End(xlUp).Row
firstrow = Sheets("SC").Cells(lastrow, "B").End(xlUp).Row

Dim lastrow As Long

Range("D7").End(xlDown).Offset(4, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(D7:D & lastrow)"

My result comes back with a #NAME, so I'm not sure I'm using correct way to find lastrow, or if something is wrong with SUM formula.

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Range("D7").End(xlDown).Offset(4, 0).FormulaR1C1 = "=sum(r7c:r[-4]c)"

 

[NinaE_11]

You made quick work of that! Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[NinaE_11]

Would there be a way I can 'set' that value of the above SUM calculation, so I can refer to it in other calculations?

 

[Fluff]

What exactly do you mean?

 

[NinaE_11]

I just ran your SUM command, which I will then need to take each of the data points of that column and divide by the SUM to get a % of each data point in a different column. (i.e. Joe's Revenue / Total Revenue = % of Revenue). Do I use the entire code you just provided to reference it in another calculation?

 

[Fluff]

Is this what you mean

Sub NinaE()
   Dim NxtRw As Long

   NxtRw = Range("D7").End(xlDown).Row
   Range("D" & NxtRw).Offset(4).FormulaR1C1 = "=sum(r7c:r[-4]c)"
   Range("E7:E" & NxtRw).FormulaR1C1 = "=rc[-1]/r" & NxtRw + 4 & "c[-1]"
End Sub

 

[NinaE_11]

Incredible! You are a STAR! Thank you AGAIN!

 

[Fluff]

Glad to help & thanks for the feedback.