+ Reply to Thread
Results 1 to 8 of 8

AverageIF in VBA

  1. #1
    Registered User
    Join Date
    02-21-2022
    Location
    Berlin
    MS-Off Ver
    365 Business
    Posts
    6

    AverageIF in VBA

    Hey everyone,

    first a big thanks to this amazing community. I have been learning from you for years <3

    I´m trying to get the average of a column without the zeros being recognized in VBA.
    The code for the regular average is working just fine, but when I exchange the average formula with AverageIf, it does not work in VBA.

    Solution with 0 recognized in VBA that works:

    Sub MittelwertDmit0()
    Range("D34").Formula = "=Average(D1:D33)"

    End Sub



    If I change the average formula with one of the following two, which is what I need, it does not work anymore. Even though in Excel both of them work just fine, just not in VBA:
    1.=AverageIf(D2:D33;">0")

    2. =Average(If(D2:D33<>0;D2:D33))


    Can someone help?

    Thanks already in advance

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: AverageIF in VBA

    Instead of:
    Please Login or Register  to view this content.
    Try:
    Please Login or Register  to view this content.
    With an averageif you need (Range, Criteria, Average range) to get the result, you may need to change my "," to a ";" for your system.

    Hope this helps

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: AverageIF in VBA

    Variant:
    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: AverageIF in VBA

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-21-2022
    Location
    Berlin
    MS-Off Ver
    365 Business
    Posts
    6

    Re: AverageIF in VBA

    Hey CheeseSandwich,

    thanks so much for your quick response.

    The new Code is:

    Sub AverageD()
    Range("D34").Formula = "=AVERAGEIF(D2:D33;">0";D2:D33)"

    End Sub

    Unfortunately now I get the same Error Message that I also got before:
    Compile Error - Expected: End of statement

    If I exclude the "" around ">0" the compile error is gone but then of course the formula itself does not work anymore.

    Any ideas for a VBA newbie?

  6. #6
    Registered User
    Join Date
    02-21-2022
    Location
    Berlin
    MS-Off Ver
    365 Business
    Posts
    6

    Re: AverageIF in VBA

    Response to mjr veverka :

    Thanks for the input. Unfortuntly crzay things happen now :D
    My data in column D got now exchanged with the numbers 1-9 in the rows 1-9

  7. #7
    Registered User
    Join Date
    02-21-2022
    Location
    Berlin
    MS-Off Ver
    365 Business
    Posts
    6

    Re: AverageIF in VBA

    SOLUTION:

    With a little adaptation the solution from mjr veverka works.

    Thanks so much for the uick help everyone.


    Sub Mittelwert()

    Dim r As Long, rr As Long, c As Long
    Dim wrng As String


    r = 2
    rr = Cells(Rows.Count, "D").End(xlUp).Row
    wrng = "$D$" & r & ":$D$" & rr
    Range("D34").Formula = "=AVERAGEIF(" & wrng & ","">0""," & wrng & ")"

    End Sub

  8. #8
    Forum Contributor
    Join Date
    10-02-2012
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    237

    Re: AverageIF in VBA

    Quote Originally Posted by Philip_ View Post
    Hey CheeseSandwich,

    thanks so much for your quick response.

    The new Code is:

    Sub AverageD()
    Range("D34").Formula = "=AVERAGEIF(D2:D33;">0";D2:D33)"

    End Sub

    Unfortunately now I get the same Error Message that I also got before:
    Compile Error - Expected: End of statement

    If I exclude the "" around ">0" the compile error is gone but then of course the formula itself does not work anymore.

    Any ideas for a VBA newbie?

    This is because you had quotations inside quotations. They need to be double quoted as per AB33s comment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Averageif help
    By Harry Basra in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2017, 06:38 AM
  2. AverageIf Help
    By 4deadinohio in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2015, 05:42 PM
  3. [SOLVED] help with =AVERAGEIF
    By fpgary in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-07-2015, 12:55 PM
  4. [SOLVED] Averageif help
    By Lewster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2015, 03:54 PM
  5. AverageIF
    By graybush in forum Excel General
    Replies: 1
    Last Post: 06-14-2012, 09:18 PM
  6. Excel 2007 : Averageif
    By PaulMc in forum Excel General
    Replies: 6
    Last Post: 09-29-2008, 03:45 AM
  7. AverageIf
    By Guikey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2008, 05:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1