Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FINDING LAST CELL IN A CALCUALTION
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FINDING LAST CELL IN A CALCUALTION
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). -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date calcualtion | Excel Worksheet Functions | |||
Calcualtion of renewal | Excel Worksheet Functions | |||
Calcualtion of renewal | Excel Worksheet Functions | |||
Calcualtion of renewal | Excel Worksheet Functions | |||
Last cell In calcualtion problem | Excel Worksheet Functions |