Thanks:  0
Likes:  0

Thread: vba average function syntax problem

1. vba average function syntax problem

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?

2. Re: vba average function syntax problem

Try

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

3. Re: vba average function syntax problem

Possibly:

Range("F" & X) = Application.Worksheetfunction.Average(Range(range("F" & X+1), Range("F" & lastrownumber)))

4. Re: vba average function syntax problem

I get an error with both codes above that state...
"unable to get the average property of the worksheetfunction class"

5. Re: vba average function syntax problem

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

6. Re: vba average function syntax problem

Originally Posted by DoingMyBest
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

7. (MS Excel 2010 VBA) average function for dynamic (variable size, first row and last row) range

Similarto question:
http://www.mrexcel.com/forum/excel-questions/620088-visual-basic-applications-average-function-syntax-problem.html
-------
I’ma beginner in using MS Excel VBA (2010).
The scope of my current project is:
Theoverall design of the database in MS Excel 2010 is:
SourceDataàCopiedDataàPreFilter:àSummData, Range1,Range2… RangeN

Thesize of each range varies with the size of SourceData and any prior datamanipulation, including
‘datacleanup’, AutoFilter, AdvancedFilter, and PivotTable, etc., indicated above byarrows.

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.

Yourhelp in finding an ‘easy’ solution, and perhaps a more elaborate solution(s)(using the overall design) would be appreciated.

SinceI am new to VBA, I would also like a ‘pedagogic explanation’, your timepermitting and where appropriate.

Thankyou.
-------

' rng3.Name = "myDollars"
' mysum =WorksheetFunction.Sum(Range("myDollars"))
'
' Dim MyRange As Range
' Set MyRange = Range(Cells(MyRow,10),Cells(MyRow1, 10))
' Avg =Application.WorksheetFunction.Average(MyRange)
'
' http://www.mrexcel.com/forum/excel-questions/43261-worksheetfunction-average.html
' Sub yAverageLastN()
' LastN = [B2]
' [c2] ="=AVERAGE(INDEX(A:A,MATCH(9E+307,A:A)):INDEX(A:A,MATCH(9E+307,A:A)-B2+1))"
' MsgBox "AverageValue is = " &[c2]
' End Sub
'
WithWSsc.Cells(ACrow + 1, 1)
.Offset(, 1).Value = "All AC Total" '<-row label in _
column 2
'Create range for column
'ACcell = Cells(PFrow + 5, 1).Address '<-recall from above
'ACrow = WSsc.Cells(Rows.Count,2).End(xlUp).Row '<-recall _
from above
to 3rd column '<-ERROR: object required
'ACqtyTop = ACcell.Offset(1, 2) '<-ERROR: object required
ACqtyTop = Cells(PFrow + 6, 3).Address '<-with today's _
dataset evaluated to C332 (correct)
ACqtyEnd = Cells(ACrow, 3).Address '<-with today's dataset _
evaluated to C387 (correct)
'ACqtyRng=WSsc.Range(ACqtyTop:ACqtyEnd) 'highlights as error, _
doesn't like ':'
ACqtyRng = WSsc.Range(ACqtyTop, ACqtyEnd)
'mysum =WorksheetFunction.Sum(Range(ACqtyRng)) '<-evaluates _
to empty
'.Offset(, 2).Value =WorksheetFunction.Sum(Range(ACqtyRng)) _
'ERROR: method of range failed
.Offset(, 2).Value =WorksheetFunction.Sum(ACqtyRng) ‘<-works!
.Offset(, 2).Name = "TotalACQty" ‘<-name cell to refer to it elsewhere
'Create range for column
ACprcTop = Cells(PFrow + 6, 4).Address '<-evaluates to D332
ACprcEnd = Cells(ACrow, 4).Address '<-evaluates to D387
ACprcRng = WSsc.Range(ACprcTop, ACprcEnd)
'.Offset(, 3).Value = Application.WorksheetFunction.Average(ACprcRng) _
'ERROR: unable to get Average property of WorksheetFunction class
'.Offset(, 3).Value = "=average(ACprcRng)" '<-In Excel interface _
embeds '=AVERAGE(ACprcRng)' in cell and evaluates to '#NAME?'
‘.Offset(,3).Value = "=average(Range(ACprcRng))" '<-same result as above
.Offset(, 3).Name = "AvgACPrc"
End With

8. 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

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•