vba average function syntax problem

DoingMyBest

New Member
Joined
Mar 9, 2012
Messages
2
Range("F" & X) = average(range("F"& X+1):range("F" & lastrownumber))

the colon gets highlighted with the following msgbox...
expected: list seperator or )

I'm sure it's an easy fix, or should I be using cells.value syntax?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try

Code:
Range("F" & X).Value = WorksheetFunction.Average(Range(Cells(X + 1, "F"), Cells(lastrownumber, "F")))
 
Upvote 0
Possibly:

Range("F" & X) = Application.Worksheetfunction.Average(Range(range("F" & X+1), Range("F" & lastrownumber)))
 
Upvote 0
I get an error with both codes above that state...
"unable to get the average property of the worksheetfunction class"
 
Upvote 0
Hi,

VoGs suggestion would be my favorite, but try to understand what you are doing by starting from some simple syntax.
Code:
Range("F1") = WorksheetFunction.Average(Range("F2:F55"))
Now replace the numbers (which are written as strings!) by variables
See what happens with those double quotes and the semicolon

"F1" becomes "F" & variable
"F2:F55" "F" & variable & ":F" & variable

So you get
Code:
Range("F" & X) = WorksheetFunction.Average(Range("F" & X + 1 & ":F" & lastrownumber))

tested using
Code:
Sub test()
Const X = 1
Const lastrownumber = 55
Range("F" & X) = WorksheetFunction.Average(Range("F" & X + 1 & ":F" & lastrownumber))
End Sub
kind regards,
Erik
 
Upvote 0
I get an error with both codes above that state...
"unable to get the average property of the worksheetfunction class"
Those codes are correct.
There is a problem with your data:
1. all cells are empty
or
2. at least one cell with an error
 
Upvote 0
(MS Excel 2010 VBA) average function for dynamic (variable size, first row and last row) range

Similarto question:<o:p></o:p>
http://www.mrexcel.com/forum/excel-questions/620088-visual-basic-applications-average-function-syntax-problem.html<o:p></o:p>
------- <o:p></o:p>
I’ma beginner in using MS Excel VBA (2010).<o:p></o:p>
The scope of my current project is:<o:p></o:p>
Theoverall design of the database in MS Excel 2010 is:<o:p></o:p>
SourceDataàCopiedDataàPreFilter:àSummData, Range1,Range2… RangeN<o:p></o:p>
<o:p></o:p>
Thesize of each range varies with the size of SourceData and any prior datamanipulation, including<o:p></o:p>
‘datacleanup’, AutoFilter, AdvancedFilter, and PivotTable, etc., indicated above byarrows.<o:p></o:p>
<o:p></o:p>
Eachrange is dynamic (variable size, firstrow and last row), contains a header row, and is separated from the next by5 rows. I want to insert Excel functions [usually =sum(), =average(), =count(),=sumif(), =countif() (at this point in time I only succeeded at =sum() bylocating specific cells bordering the column range to sum)] in the row beloweach range. These calculated fields willfurther be used to populate a summary table on the same sheet, as well as asummary sheet/workbook.<o:p></o:p>
<o:p></o:p>
Yourhelp in finding an ‘easy’ solution, and perhaps a more elaborate solution(s)(using the overall design) would be appreciated.<o:p></o:p>
<o:p></o:p>
SinceI am new to VBA, I would also like a ‘pedagogic explanation’, your timepermitting and where appropriate.<o:p></o:p>
<o:p></o:p>
Thankyou.<o:p></o:p>
------- <o:p></o:p>
‘<o:p></o:p>
'[2V]---Add totals to vertically-placed data<o:p></o:p>
' http://www.ozgrid.com/forum/showthread.php?t=37718<o:p></o:p>
' rng3.Name = "myDollars"<o:p></o:p>
' mysum =WorksheetFunction.Sum(Range("myDollars"))<o:p></o:p>
'<o:p></o:p>
' https://www.google.ca/#q=vba+average+function<o:p></o:p>
' Dim MyRange As Range<o:p></o:p>
' Set MyRange = Range(Cells(MyRow,10),Cells(MyRow1, 10))<o:p></o:p>
' Avg =Application.WorksheetFunction.Average(MyRange)<o:p></o:p>
'<o:p></o:p>
' http://www.mrexcel.com/forum/excel-questions/43261-worksheetfunction-average.html<o:p></o:p>
' Sub yAverageLastN()<o:p></o:p>
' LastN = [B2]<o:p></o:p>
' [c2] ="=AVERAGE(INDEX(A:A,MATCH(9E+307,A:A)):INDEX(A:A,MATCH(9E+307,A:A)-B2+1))"<o:p></o:p>
' MsgBox "AverageValue is = " &[c2]<o:p></o:p>
' End Sub<o:p></o:p>
'<o:p></o:p>
WithWSsc.Cells(ACrow + 1, 1)<o:p></o:p>
.Offset(, 1).Value = "All AC Total" '<-row label in _<o:p></o:p>
column 2 <o:p></o:p>
'Create range for column<o:p></o:p>
'ACcell = Cells(PFrow + 5, 1).Address '<-recall from above<o:p></o:p>
'ACrow = WSsc.Cells(Rows.Count,2).End(xlUp).Row '<-recall _<o:p></o:p>
from above<o:p></o:p>
'ACqtyTop = ACcell.Offset(1, 2).Address'<-less header row, _<o:p></o:p>
to 3rd column '<-ERROR: object required<o:p></o:p>
'ACqtyTop = ACcell.Offset(1, 2) '<-ERROR: object required<o:p></o:p>
ACqtyTop = Cells(PFrow + 6, 3).Address '<-with today's _<o:p></o:p>
dataset evaluated to C332 (correct)<o:p></o:p>
ACqtyEnd = Cells(ACrow, 3).Address '<-with today's dataset _<o:p></o:p>
evaluated to C387 (correct)<o:p></o:p>
'ACqtyRng=WSsc.Range(ACqtyTop:ACqtyEnd) 'highlights as error, _<o:p></o:p>
doesn't like ':'<o:p></o:p>
ACqtyRng = WSsc.Range(ACqtyTop, ACqtyEnd)<o:p></o:p>
'mysum =WorksheetFunction.Sum(Range(ACqtyRng)) '<-evaluates _<o:p></o:p>
to empty<o:p></o:p>
'.Offset(, 2).Value =WorksheetFunction.Sum(Range(ACqtyRng)) _<o:p></o:p>
'ERROR: method of range failed<o:p></o:p>
.Offset(, 2).Value =WorksheetFunction.Sum(ACqtyRng) ‘<-works!<o:p></o:p>
.Offset(, 2).Name = "TotalACQty" ‘<-name cell to refer to it elsewhere<o:p></o:p>
'Create range for column<o:p></o:p>
ACprcTop = Cells(PFrow + 6, 4).Address '<-evaluates to D332<o:p></o:p>
ACprcEnd = Cells(ACrow, 4).Address '<-evaluates to D387<o:p></o:p>
ACprcRng = WSsc.Range(ACprcTop, ACprcEnd)<o:p></o:p>
'.Offset(, 3).Value = Application.WorksheetFunction.Average(ACprcRng) _<o:p></o:p>
'ERROR: unable to get Average property of WorksheetFunction class<o:p></o:p>
'.Offset(, 3).Value = "=average(ACprcRng)" '<-In Excel interface _<o:p></o:p>
embeds '=AVERAGE(ACprcRng)' in cell and evaluates to '#NAME?'<o:p></o:p>
‘.Offset(,3).Value = "=average(Range(ACprcRng))" '<-same result as above<o:p></o:p>
.Offset(, 3).Name = "AvgACPrc"<o:p></o:p>
End With<o:p></o:p>
 
Upvote 0
Re: (MS Excel 2010 VBA) average function for dynamic (variable size, first row and last row) range

Hi, WELCOME to the Board!
you responded to an old thread; it would be better if you started a new one.

kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top