FINDING LAST CELL IN A CALCUALTION

[Jeff]

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

 

[Dick Kusleika]

Jeff

The thoery is good, I just think the syntax is a little off. This uses a
variable for ease of reading.

Dim lLastRow as Long

'Assume you want H here and not K, so I used 8
With xlApp.Sheets("Earned Value")
lLastRow = .Cells(.Rows.Count, 8).End(xlUp).Row
End With

xlApp.ActiveSheet.Range("K7").Formula = "=AVERAGE('Earned Value'! H2:H" &
lLastRow & ")"

If you are automating Excel from another application, you may have a problem
with the built-in constant xlUp (literal value = -4162).