Results 1 to 8 of 8

vba average function syntax problem

This is a discussion on vba average function syntax problem within the Excel Questions forums, part of the Question Forums category; Range("F" & X) = average(range("F"& X+1):range("F" & lastrownumber)) the colon gets highlighted with the following msgbox... expected: list seperator or ...

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Default 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. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,153

    Default Re: vba average function syntax problem

    Try

    Code:
    Range("F" & X).Value = WorksheetFunction.Average(Range(Cells(X + 1, "F"), Cells(lastrownumber, "F")))
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular TMShucks's Avatar
    Join Date
    Jan 2011
    Location
    Manchester, UK
    Posts
    356

    Default Re: vba average function syntax problem

    Possibly:

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

  4. #4
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Default 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. #5
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default 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. #6
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default Re: vba average function syntax problem

    Quote Originally Posted by DoingMyBest View Post
    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. #7
    New Member
    Join Date
    Mar 2014
    Posts
    7

    Default (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.
    -------

    '[2V]---Add totals to vertically-placed data
    ' http://www.ozgrid.com/forum/showthread.php?t=37718
    ' rng3.Name = "myDollars"
    ' mysum =WorksheetFunction.Sum(Range("myDollars"))
    '
    ' https://www.google.ca/#q=vba+average+function
    ' 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
    'ACqtyTop = ACcell.Offset(1, 2).Address'<-less header row, _
    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. #8
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com