Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA - Determining average value in range using VBA (Excel 79 SR-2)

    Duh! I cannot for the life of me figure out the VBA Editor interface and how to perform otherwise simple instructions to perform a task. Here's what I want to do...
    I want to add some code to an existing VBA macro that determines the average value of a range (will typically always be C1:C8000) and assign it to a variable that I can use in another instruction further on in the macro. The Excel function is simply '=AVERAGE(range)'.

    I am sure it will be a simple one line solution but I cannot figure out that VBA object browser, etc.

    THANKS in ADVANCE for ALL YOUR HELP!!!

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA - Determining average value in range using VBA (Excel 79 SR-2)

    If it's a predetermined range, then you can always leave off the = and add []:

    sngAverage = [Average(C1:C8000)]

    If the range changes, then use the Application.Evaluate (type this in VBA, then press F1 for help) form with Average(C1:C8000) in the string to be evaluated. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Determining average value in range using VBA (Excel 79 SR-2)

    The following should do what you want:

    <pre>Dim dblAverage As Double
    dblAverage = Application.WorksheetFunction.Average(Worksheets(" Sheet1").Range("C1:C8000"))
    </pre>

    Legare Coleman

Posting Permissions

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