auto sum data above

[preseb]

I have a macro that is getting me to the cell that I need to run a calucation (=sumproduct(J2:J16,K2:K16)/sum(K2:K16)
the problem is that range listed (2:16) will always change. how do I sum the above?

Thanks

[CatDaddy]

what do you mean the range will always change?

[preseb]

That it is not always 2:16. It may be 2:14 or 2:18

[CatDaddy]

Dim EndCell As String
EndCell = 'some criteria, could be inputbox?
Cells(somecell).FormulaR1C1 = "=SUMPRODUCT(J2:J" & EndCell & ",K2:K" & Endcell & ")/SUM(K2:K" & Endcell & ")"

[Chabu]

or if you want it as a worksheetfunction

Public Function sumpr(target As Range) As Double
With Application.WorksheetFunction
sumpr = .SumProduct(target, target.Offset(, 1)) / .Sum(target)
End With
End Function

[mikerickson]

Use a dynamic Named Range.

What determines which on which row the range ends?

[preseb]

mikerickson - What I am trying to do is at the bottom of the data to sum up until I hit a cell with no data in it.

[mikerickson]

Something like

Dim myRange as Range
With Columns(11)
With .Cells(.Rows.Count,1).End(xlup)
Set myRange = Range(.Cells, .End(xlup))
End With
End With

[Grade4.2]

To write a macro that can calculate the sum of a range of cells that may change, you can use the "End" and "Down" methods of the Range object to find the last row with data in it, and then use the "Resize" method of the Range object to create a new range that includes all the cells up to the last row. Here is an example:


Sub CalculateSum()
Dim lastRow As Long
lastRow = Range("J" & Rows.Count).End(xlUp).Row
' Create a new range that includes all the cells up to the last row
Dim rng As Range
Set rng = Range("J2").Resize(lastRow - 1)
' Calculate the sum of the range of cells
Dim sum As Double
sum = Application.WorksheetFunction.Sum(rng)
End Sub


In the code above, the "CalculateSum" subroutine finds the last row with data in column J, creates a new range of cells that includes all the cells up to the last row, and then calculates the sum of the range of cells.

To use this code in your macro, you can replace the range of cells in your calculation with the "rng" variable, like this:


Sub CalculateSum()
Dim lastRow As Long
lastRow = Range("J" & Rows.Count).End(xlUp).Row
Dim rng As Range
Set rng = Range("J2").Resize(lastRow - 1)
' Use the "rng" variable in the calculation
Dim result As Double
result = Application.WorksheetFunction.Sumproduct(rng, rng) / Application.WorksheetFunction.Sum(rng)
End Sub


In the code above, the "result" variable will contain the result of the calculation using the dynamic range of cells. You can then use this variable in your macro as needed.