SumProduct function

  • Thread starter Thread starter 21MSU
  • Start date Start date
2

21MSU

I am creating the following Excel function

Function BlendFlashPt_degF(volpct_data As Range, CompFlashPt_degF As
Range) As Variant

Dim FPI As Double

FPI = Application.WorksheetFunction.SumProduct(volpct_data, 10 ^
(-6.1188 + (4345.2 / ((CompFlashPt_degF) + 383))))

BlendFlashPt_degF = (4345.2 / (Log(FPI) / 2.302585092994 + 6.1188)) -
383

End Function

But, I am getting

run-time error 13
'type mismatch'

on the line
FPI = Application.WorksheetFunction.SumProduct(volpct_data, 10 ^
(-6.1188 + (4345.2 / ((CompFlashPt_degF) + 383))))

However, when I typed this formula

SUMPRODUCT(B17:M17,10^(-6.1188+(4345.2/((B37:M37)+383))))

into Excel's formula bar it worked.
where B17:M17 represents volpct_data
B37:M37 represents CompFlashPt_degF

What I am doing wrong in my user-defined function?

Please Help!
Thanks!
 
Try this

Replace

FPI = Application.WorksheetFunction.SumProduct(volpct_data, 10 ^ (-6.1188 +
(4345.2 / ((CompFlashPt_degF) + 383))))

with

FPI = Application.Evaluate("SumProduct(" & volpct_data.Address & _
", 10 ^ (-6.1188 + (4345.2 / ((" & CompFlashPt_degF.Address & ") +
383))))")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top