WorksheetFunction.Sum returning zero for numbers

Evan

Clean up your code, kiddies. After much wailing and gnashing of teeth, the problem turned out to be the extra parentheses I had in my .Sum arguments. Big thanks to @BigBen and @JvdV.

The Problem: Worksheetfunction.Sum returns a sum of 0 for a dynamic range, but only for rows.count > 1 and only for currency-formatted reference data.

More Detail: I have a userform set up to scrape a reference workbook and return a different number into each of four different textboxes based on user input. On occasion the numbers will need to be a sum of several rows on the reference workbook. Using my code below, this works like a dream for every return textbox as long as the number of rows is 1, but returns 0 (or $0.00 as it were) for anything else. However, it works just fine in all circumstances for the one sum that is just an integer. The rest are formatted as currency.

내가 한 일 : MsgBoxes를 사용하여 동적 범위가 올바른 주소, 즉 합계를 원하는 모든 셀을 반환하고 해당 주소의 숫자가 텍스트가 아니라 실제로 숫자인지 확인했습니다 (IsNumber에 대한 True 반환으로 확인 됨). . .Subtotal 및 .Aggregate를 사용하여 도움이 될 수 있는지 확인했지만 누락 된 개체 및 기타 오류가 발생하여 VBA를 처음 사용했기 때문에 엉뚱한 소리를 냈습니다.

코드:

내 기본 논리는 다음과 같습니다. textbox.value에 대한 모든 참조 (csrWorkbook) 시트에서 검색합니다. 발견되면 병합 된 영역의 높이를 측정합니다 (내가 알고 있지만 병합 결정은 내 급여보다 높음). 4 개의 다른 관련 수량을 찾으려면 오른쪽으로 오프셋하십시오. 여러 행이있는 경우이 수량을 합산하십시오. 합계를 4 개의 다른 텍스트 상자로 반환합니다.

도움!

    Private Sub ScrapeButton_Click()

    'Enter search term into first TB
    'click search button
    'result DOES(!!) appear in second TB

    'Variables
    Dim csrWorkbook As Workbook
    Dim refWorkbook As Workbook
    Dim refVariables As Worksheet
    Dim csrFilePath As String
    Dim csrFileName As String
    Dim slinAddress As String
    Dim ws As Worksheet
    Dim sheetCount As Long
    Dim rowCount As Long
    Dim slinCell As Excel.Range
    Dim quantCells As Excel.Range
    Dim costCells As Excel.Range
    Dim feeCells As Excel.Range
    Dim totalCells As Excel.Range
    Dim i As Integer
    Dim iCost As Double
    Dim iFee As Double
    Dim iTotal As Double


    Set refWorkbook = Workbooks("AutomationBackbone.xlsm")
    csrFileName = refWorkbook.Sheets("Variable Storage").Range("A2").Value
    Set csrWorkbook = Workbooks(csrFileName)

    sheetCount = csrWorkbook.Sheets.Count

    'search all worksheets for data in a known column
    For i = 1 To sheetCount
        Set slinCell = csrWorkbook.Sheets(i).Range("C1:C100").find(Me.TextBox1.Value)
            If Not slinCell Is Nothing Then

                'Find sums and populate
                rowCount = slinCell.MergeArea.Rows.Count 'count the number of rows in merged area
                Set quantCells = slinCell.Offset(0, 2).Resize(rowCount, 1) 'establish a new range of that same height
                Set costCells = quantCells.Offset(0, 6)
                Set feeCells = quantCells.Offset(0, 7)
                Set totalCells = quantCells.Offset(0, 8)

                Me.iQuantityTB.Value = Application.WorksheetFunction.Sum((quantCells)) 'populate the Initial Quantity
                iCost = Application.WorksheetFunction.Sum((costCells)) 'find sum of Cost range
                iFee = Application.WorksheetFunction.Sum((feeCells)) 'find sum of Fee range
                iTotal = Application.WorksheetFunction.Sum((totalCells)) 'find sum of Total range

                Me.iCostTB.Value = iCost 'populate textboxes
                Me.iFeeTB.Value = iFee
                Me.iTotalTB.Value = iTotal

'original code commented out to see if being more explicit helped.
     'Narrator: it didn't
                'Me.iCostTB.Value = Application.WorksheetFunction.Sum((quantCells.Offset(0, 6))) 'populate the Initial Cost
                'Me.iFeeTB.Value = Application.WorksheetFunction.Sum((quantCells.Offset(0, 7))) 'populate the Initial Fee
                'Me.iTotalTB.Value = Application.WorksheetFunction.Sum((quantCells.Offset(0, 8))) 'populate the Initial Total

                Exit Sub
            End If
        Next i
    End Sub

Edit: Pics added for clarity.

This approximates the format of the data I'm trying to pull and sum to populate textboxes

BigBen

Parentheses strike again!


Including the extra parentheses causes the inner expression to be evaluated, and the result to be passed to Sum.

So

Application.WorksheetFunction.Sum((costCells))

is equivalent to

Application.WorksheetFunction.Sum(costCells.Value)

which returns zero when the underlying data is Currency.

As a small reproducible example for Sums behavior here (which is not what I expected):

Dim x(0 To 1) As Currency
x(0) = 1
x(1) = 2

Debug.Print Application.Sum(x) '<~ returns zero.

Note that .Value2 does not use the Currency data type, and the line

Application.WorksheetFunction.Sum(costCells.Value2)

would return the correct result regardless of the underlying value.

Note that similar behavior has been noted and explained here.

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정
0

몇 마디 만하겠습니다

0리뷰
로그인참여 후 검토

관련 기사

분류에서Dev

Get all the possible combinations of the given numbers to reach at a given sum

분류에서Dev

Application.WorksheetFunction 대 WorksheetFunction

분류에서Dev

WorksheetFunction.Sum은 숫자에 대해 0을 반환합니다.

분류에서Dev

mongodb $ sum only postive Numbers

분류에서Dev

Sum numbers return from a php function

분류에서Dev

Hazel bash script or Applescript to sum numbers in txt file

분류에서Dev

Create list of numbers mirrored around zero (python)

분류에서Dev

Application.WorksheetFunction.Match 메서드

분류에서Dev

How can I zero-pad numbers in a Batch FOR loop?

분류에서Dev

fscanf returning random large numbers

분류에서Dev

Excel VBA의 WorksheetFunction 문제

분류에서Dev

null 대신 SUM zero (0) 반환

분류에서Dev

What does Application.WorksheetFunction.Find do?

분류에서Dev

rounding SQL decimal zero and then sum

분류에서Dev

JS output sum (only the numbers) of all checked input fields values before form submit

분류에서Dev

Print the Sum of the largest of two numbers with the collections of array method?

분류에서Dev

Find m numbers that sum to n in a sequence

분류에서Dev

how to store zero leading numbers in mongoDB

분류에서Dev

mean of non zero elements - why are these two attempt returning different results?

분류에서Dev

Creating a Sum of numbers app with JavaScript

분류에서Dev

SQL SUM amouts by distinct bill's numbers

분류에서Dev

Conditional SUM not returning any value

분류에서Dev

Racket recursive function for returning the sum of a list of squared numbers

분류에서Dev

Sum numbers in filenames in a directory using command line

분류에서Dev

input and sum the numbers - java

분류에서Dev

Find numbers with largest sum (Javascript)

분류에서Dev

Getting SUM of MySQL table data when there is floating point numbers

분류에서Dev

worksheetfunction.median issue with range

분류에서Dev

VBA Excel WorksheetFunction.Rank 문제

Related 관련 기사

  1. 1

    Get all the possible combinations of the given numbers to reach at a given sum

  2. 2

    Application.WorksheetFunction 대 WorksheetFunction

  3. 3

    WorksheetFunction.Sum은 숫자에 대해 0을 반환합니다.

  4. 4

    mongodb $ sum only postive Numbers

  5. 5

    Sum numbers return from a php function

  6. 6

    Hazel bash script or Applescript to sum numbers in txt file

  7. 7

    Create list of numbers mirrored around zero (python)

  8. 8

    Application.WorksheetFunction.Match 메서드

  9. 9

    How can I zero-pad numbers in a Batch FOR loop?

  10. 10

    fscanf returning random large numbers

  11. 11

    Excel VBA의 WorksheetFunction 문제

  12. 12

    null 대신 SUM zero (0) 반환

  13. 13

    What does Application.WorksheetFunction.Find do?

  14. 14

    rounding SQL decimal zero and then sum

  15. 15

    JS output sum (only the numbers) of all checked input fields values before form submit

  16. 16

    Print the Sum of the largest of two numbers with the collections of array method?

  17. 17

    Find m numbers that sum to n in a sequence

  18. 18

    how to store zero leading numbers in mongoDB

  19. 19

    mean of non zero elements - why are these two attempt returning different results?

  20. 20

    Creating a Sum of numbers app with JavaScript

  21. 21

    SQL SUM amouts by distinct bill's numbers

  22. 22

    Conditional SUM not returning any value

  23. 23

    Racket recursive function for returning the sum of a list of squared numbers

  24. 24

    Sum numbers in filenames in a directory using command line

  25. 25

    input and sum the numbers - java

  26. 26

    Find numbers with largest sum (Javascript)

  27. 27

    Getting SUM of MySQL table data when there is floating point numbers

  28. 28

    worksheetfunction.median issue with range

  29. 29

    VBA Excel WorksheetFunction.Rank 문제

뜨겁다태그

보관