Consulting

Results 1 to 8 of 8

Thread: #VALUE! Error for Single Criterion Sumproduct Function

  1. #1

    #VALUE! Error for Single Criterion Sumproduct Function

    Hello, I’m new to VBA and the forum, so please be kind!

    I have been working on a project at work to reformat and do useful things with difficult to read data that outputs from our system. I’ve gotten pretty far on it, but I'm at a loss regarding my current step. What I am trying to do is create a graph to display approval timing by month, but can’t get past the “sumproduct” lines, shown below. I'm against using year with this formula, because I'd like this macro to be able to be used for future projects that span other years as well as this current one, attached.

    I’ve seen other threads that address the exact same thing, but I get confused when trying to understand the help that is offered. The output I’m getting is a #VALUE! Error. I’ve searched the internet for explanations as to why I might be getting the error, but so far none make sense with the formula, since most reference different size ranges being called for the SUMPRODUCT formula. I can’t imagine this is the case here, since only one range is called out.

    ws.Range("Z2:AQ" & lastRow).NumberFormat = "mm/dd/yyyy"
     
    wc.Cells(1, 4) = [=Application.SumProduct(--('ws'.Range("AN2:AN" & lastRow) <> ""),--(Month('ws'.Range("AN2:AN" & lastRow)) = 1)]
    wc.Cells(2, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 2))]
    wc.Cells(3, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 3))]
    wc.Cells(4, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 4))]
    wc.Cells(5, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 5))]
    wc.Cells(6, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 6))]
    wc.Cells(7, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 7))]
    wc.Cells(8, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 8))]
    wc.Cells(9, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 9))]
    wc.Cells(10, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 10))]
    wc.Cells(11, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 11))]
    wc.Cells(12, 4) = [=Application.SumProduct(--(Month('ws'.Range("AN2:AN" & lastRow)) = 12))]
    ^^Each of these give #VALUE! Error

    Charts.Add
    With ActiveChart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=wc.Range("D1:D12"), PlotBy:=xlColumns
    ^^As a result of the Errors above, I get “System Error &H800401A8 (-2147221080)”
        .Location Where:=xlLocationAsObject, Name:="Graphical Summary"
        .HasLegend = False
        .HasTitle = True
        .ChartTitle.Text = "PPAP Submission By Month"
        .SeriesCollection(1).XValues = wc.Range("C1:C12")
    End With
    'Make Top Left Corner start at H18
    Dim CellsToCover3 As Range
    Dim Cht3 As ChartObject
    Set CellsToCover3 = wg.Range("H18:N35")
    Set Cht3 = ActiveChart.Parent
    Cht3.Height = CellsToCover3.Height
    Cht3.Width = CellsToCover3.Width
    Cht3.Top = CellsToCover3.Top
    Cht3.Left = CellsToCover3.Left
    Because of the size of my code, I’m only posting a small bit here. If needed, my entire code is in the attached Word Doc, and the starting file is in the excel doc.

    Additional note: I'm sure there are "cleaner" ways of doing the same thing, and I'm 100% willing to take suggestions to clean up my code and make it more efficient, but I'm mostly concerned with function for now.

    Please help!
    PepsiCells&Analysis_Single Macro.docxPepsi.xlsx

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,716
    Location
    The issue is that you cannot perform array comparisons like that in VBA.

    I would suggest you redo the calculations using Evaluate:

    wc.Cells(2, 4) = ws.Evaluate("SumProduct(--(Month(AN2:AN" & lastRow & ")=2)")
    for example
    Be as you wish to seem

  3. #3
    Thanks for replying! Unfortunately, when I copied in your example above, it's still gave me a Value Error. I also tried adding "Application." ahead of the Sumproduct function, but that produced the same result. Any thoughts on this would be much appreciated. I've been banging my head against the wall on this one for days.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,716
    Location
    Sorry - missed a bracket out. It should be:

    wc.Cells(2, 4) = ws.Evaluate("SumProduct(--(Month(AN2:AN" & lastRow & ")=2))")
    Be as you wish to seem

  5. #5
    Thank you! That worked beautifully. I need to do the same thing with January, and I read somewhere that January was different with Sumproduct, and that it would count blanks as January unless you told the code not to.

    This is what I currently have for the "January" code:

    wc.Cells(1, 4) = ws.Evaluate("SumProduct(--(Month(AN2:AN" & lastRow & ")=1))", --(Range("AN2:AN" & lastRow) <> ""))
    is this right, or close anyway? When I try to run the code this way, it doesn't work. It gives me an error saying I'm applying the wrong number of arguments. Please help!

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,716
    Location
    Close-ish.

    wc.Cells(1, 4) = ws.Evaluate("SumProduct((Month(AN2:AN" & lastRow & ")=1)*(AN2:AN" & lastRow & "<>""""))")
    Be as you wish to seem

  7. #7
    This appears to fix my issue. Thanks! You're AWESOME!!

    I am now getting an automation error when trying to form the graph, but that appears to be unrelated to this thread or the sumproduct function.

    Thanks again!

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,716
    Location
    Glad to help!
    Be as you wish to seem

Posting Permissions

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