How to get last cell in "Avg()" calcualtion

  • Thread starter Thread starter Buster
  • Start date Start date
B

Buster

I am trying to average a series of numbers of a second sheet within
the same workbook. My equation is:
xlApp.Range("K7").Formula = "=AVERAGE('Earned Value'! H2,H500)"

I would like to use something similar to the following to ensure I get
the last cell.

xlApp.Range("K7").Formula = "=AVERAGE('Earned Value'! H2,H &
xlApp.ActiveSheet.Cells(xlApp.Rows.Count, ""k"").End(xlUp).Row"

Where have I gone wrong?

THanks
JEff
 
Hi
not tested but try:
xlApp.Range("K7").Formula = "=AVERAGE('Earned Value'! H2:H" &
xlApp.ActiveSheet.Cells(xlApp.Rows.Count, "k").End(xlUp).Row & ")"
 
Frank, I get a result except it returns h1:h2 and not the range of the
sheet. ANy ideas

Jeff
 
I like to let excel do the work for me:

dim myRng as range
with worksheets("earned value")
set myrng = .range("H2",.cells(.rows.count,"H").end(xlup))
end with

'I'm not sure what xlApp is:
???.range("k7").formula _
= "=average(" & myrng.address(external:=true) & ")"

I would have guessed that xlApp was the excel application. Are you running this
from some external program (VB??). If yes, make sure you qualify that "myrng"
stuff.
 
Hi
what do you mean with 'range of sheets'. Do you mean
xlApp.Range("K7").Formula = "=AVERAGE('Earned Value:" &
activesheet.name & "'! H2:H" &
xlApp.ActiveSheet.Cells(xlApp.Rows.Count, "k").End(xlUp).Row & ")"
 
Back
Top