Mismatch for Sumproduct

[mikejo]

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!

[mikejo]

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

[Aussiebear]

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.

[mikejo]

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

[p45cal]

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.