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

[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

 

[Frank Kabel]

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

 

[Buster]

Frank, I get a result except it returns h1:h2 and not the range of the
sheet. ANy ideas

Jeff

 

[Dave Peterson]

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.

 

[Frank Kabel]

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 & ")"