+ Reply to Thread
Results 1 to 3 of 3

Array worksheetFunction.sum

  1. #1
    Registered User
    Join Date
    12-13-2020
    Location
    Portland, Oregon
    MS-Off Ver
    O365
    Posts
    4

    Array worksheetFunction.sum

    Hi - see code below. I've built a simple 3 element array with integers in each box. Using the worksheetFunction.sum I can sum the total array, but when I change the array values to Currency$, the sum just returns a value of 0. I need to be able to sum currency values in the array, but it's not working.



    Sub test22()

    Range("j165").Select

    Dim arryTest2 As Variant

    arryTest2 = Range("h160").CurrentRegion 'array values equal 200, 25, 5, total sum is 230. changes the values to $200, $25, $5, total sum is 0

    ActiveCell.value = WorksheetFunction.Sum(arryTest2)
    End Sub
    Last edited by olsonkyle12; 01-18-2021 at 03:24 PM.

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

    Re: Array worksheetFunction.sum

    Quote Originally Posted by olsonkyle12 View Post
    ... when I change the array values to Currency$ ... it's not working
    Please Login or Register  to view this content.
    Ad 1. WorksheetFunction.Sum Method => from the context help MS
    -----------------------------------------------------------------
    "Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted.
    If an argument is an array or reference, only numbers in that array or reference are counted.
    Empty cells, logical values, or text in the array or reference are ignored.
    Arguments that are error values or text that cannot be translated into numbers cause errors."


    Ad 2. WorksheetFunction.IsNumber Method => from the context help MS
    ---------------------------------------------------------------------
    "The value arguments of the IS functions are not converted.
    For example, in most other functions where a number is required, the text value "19" is converted to the number 19.
    However, in the formula ISNUMBER("19"), "19" is not converted from a text value, and the ISNUMBER function returns FALSE.
    The IS functions are useful in formulas for testing the outcome of a calculation.
    When combined with the IF function, they provide a method for locating errors in formulas (see the following examples)."

    Ad 3. Code:
    Please Login or Register  to view this content.
    Summa summarum:
    MS ignores you and your currency ... ....

  3. #3
    Registered User
    Join Date
    12-13-2020
    Location
    Portland, Oregon
    MS-Off Ver
    O365
    Posts
    4

    Re: Array worksheetFunction.sum

    Okay, thx. Makes sense.

+ 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] Worksheetfunction.min not working with array
    By SJGORILLA in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-31-2019, 06:01 AM
  2. [SOLVED] vba for using WorksheetFunction with array formula
    By Andy Swain in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2019, 11:52 AM
  3. Array Formula Using WorksheetFunction
    By tbaker818 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2017, 03:41 AM
  4. Equivalent of worksheetfunction.sumif for an array
    By Rschwar23 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-08-2015, 02:17 PM
  5. WorksheetFunction.Percentile - Array Formula
    By Fjcosta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2015, 02:54 PM
  6. Array Formula using Application.WorksheetFunction
    By Sonada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2009, 06:47 PM
  7. Array Formula Using WorksheetFunction
    By xcelion in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-18-2005, 07:15 AM

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