Consulting

Results 1 to 5 of 5

Thread: Mismatch for Sumproduct

  1. #1

    Mismatch for Sumproduct

    Hi,

    I am trying to add values to a range. Earlier I used to give a formula to first cell and extend, but I was required to set calculation to automatic in order to process those cells and it started to run too slow. I am trying to add the values to an array and then add the array back as values, but I am getting a mismatch error for a worksheetfunction.sumproduct and I can't figure the cause. Any help would be appreciated!

    For i = 1 to 3 ' this would be the column
        For r = 3 To lRow
            dataArray(r, 1) = WorksheetFunction.SumProduct( _
            (Right(ws.Range("D2: D" & lRow), 4) = ws.Cells(2, lCol + 1 + i).Value) * _
            (ws.Range("R2:R" & lRow) = ws.Cells(r, "B").Value) * _
            (ws.Range("AC2:AC" & lRow)))
    r, lCol, lRow are long, dataArray is variant: ReDim dataArray(1 To lRow, 1 To 1)

    Error is run-time error 13 - type mismatch

    Thanks!
    Last edited by mikejo; 09-23-2023 at 04:57 AM. Reason: Added code tags to supplied code

  2. #2
    I just saw the sub-forum dedicated to this, sorry for wrong location of topic

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    Mikejo , don't worry I'll fix that for you. BTW, when posting code to this forum, please wrap your code with code tags. See the first line in my signature for a hint as to how we prefer to see code when posted.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Thank you, changed. Follow up on the topic: Even if I reduce the formula to something simpler, I still get mismatch:
    WorksheetFunction.SumProduct( _        
            (ws.Range("AX2:AX" & lRow) = "value") * _
            (ws.Range("R2:R" & lRow) = "value") * _
            (ws.Range("AC2:AC" & lRow)))
    Tested the 2 values in quotes to be present in their respective columns, while AC a column full of numbers

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    1. Your type mismatch is from multiple instances in the sumproduct line where you're trying to get arrays (zz=ws.Range("AX2:AX" & lRow) = "value" alone will get you a mismatch error).
    2. You're looking for speed; your vba may not get you much extra speed because you're repeatedly reading from the sheet (and evaluating the same things over and over). This can be streamlined a lot.
    3. It's difficult to guess what you want the code to do, from code which isn't doing what you want! I suggest you attach a workbook, with say, some 10 rows of data, which includes your existing (and working) formulae. Highlight the range which needs to be replaced by vba. The workbook should include only the code related to this problem (so that we don't have to go hunting for it) and only the sheets needed for this problem.
    4. It may also help us if you say in words what the results are meant to show.
    5. What version of Excel are you using?

    The bits coloured in this colour are what I'd like to see in your reply.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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