Sumproduct error

[K]

Hi all, I got macro below which is not working as I get error syaing
"Type mismatch" and on clicking Debug button it highlights the below
line

c.Offset(0, 3).Value = Application.Evaluate("Sumproduct((" & RngC
= .Range("E1").Value & c.Offset(0, 1).Value & c.Offset(0, -1).Value &
")*(" & RngD & "))")

Please can any friend help me on this that what am I doing wrong

Sub PrepBlendDataRev()
Estlstcl = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
RngC = Sheets("Data").Range("A2:A" & Estlstcl)
RngD = Sheets("Data").Range("I2:I" & Estlstcl) '.Range("G2:G" &
Estlstcl) for Est without Adj

With Sheets("Record")
lastcl2 = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each c In .Range("B2:B" & lastcl2).Cells

c.Offset(0, 3).Value = Application.Evaluate("Sumproduct((" & RngC
= .Range("E1").Value & c.Offset(0, 1).Value & c.Offset(0, -1).Value &
")*(" & RngD & "))")

Next
End With
End Sub

 

[Nick H]

Hi K,

This is going to be difficult without seeing your data and having a
clear understanding of what you're trying to do. I'm going to have to
make some assumptions but even then I'm not sure I can be of much help
other than pointing out things I think your doing wrong.

First of all you need to turn on Option Explicit and dimension your
variables. This will force you to write better code and save you a
world of pain when it comes to debugging.

First I'm going to assume that RngC and RngD are supposed to be ranges
in which case when you set them equal to a range you must use the
'Set' keyword e.g.

Set RngC = Sheets("Data").Range("A2:A" & Estlstcl)

Using Option Explicit would have pointed this out as a problem early
on.

Then you try and make RngC equal to three values that you concatenate
as strings...

RngC = .Range("E1").Value & c.Offset(0, 1).Value & c.Offset(0,
-1).Value

....and attempt to multiply with RngD

Online Help states that the array arguments must have the same
dimensions so I would start by setting the ranges RngC and RngD
outside of the formula and try and end up with osmething that looks
like this...

Application.Evaluate("Sumproduct((" & RngC & ")*(" & RngD & "))")

....I'm just going to trust you on the double quotes.

In fact how about something like this (untried and untested and I've
no idea what your data looks like so don't expect it to work without
tweaking )...

Option Explicit 'At the top of every module!

Sub PrepBlendDataRev()
Dim lastcl2 As Long
Dim RngC As Range
Dim RngD As Range
Dim c As Range

With Sheets("Record")
lastcl2 = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each c In .Range("B2:B" & lastcl2).Cells
Set RngC = Sheets("Data").Range("A" & c.Row)
Set RngC = Sheets("Data").Range("I" & c.Row)
c.Offset(0, 3).Value = Application.Evaluate("Sumproduct(("
& RngC & ")*(" & RngD & "))")
Next
End With
End Sub


HTH Br, Nick

 

[John Bundy]

I got it to work with this, also know that your c.offset stuff will have to
be calculated seperately because the arrays in a calculation must be the
same. Your offset section provides 3 numbers while RngC and RngD can be any
number.
Application.WorksheetFunction.SumProduct(RngC) *
Application.WorksheetFunction.SumProduct(RngD)