Excel - Sumproduct formula works in spreadsheet, NOT VBA. Help please

Asked By DogLover

20-Nov-09 09:35 PM

Basically, I want to replace this formula with VBA Code

I can get this one to work to sum
=SUMPRODUCT(--(DataTime="First day of employment (Time
1)"),--(DataPosition=N6),(DataQuestion1)

When I change this to Count rather than Sum, my Code Evaluates as an Error
=SUMPRODUCT(--(DataTime="First day of employment (Time
1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))

The only line of code I switch out is the last mFormula row.  The line of
code that works I have commented out.  I am really stumped...

This is the code that I have..

Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mQuestion1Criteria As String
Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mQuestion1Range As Range
Dim mFormula As String
Dim Kountifs As Variant   'could be an error

mTimeCriteria = "First day of employment (Time 1)"
mPositionCriteria = "Registered Nurse"
mQuestion1Criteria = "*"

With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mQuestion1Range = .Range("DataQuestion1")

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
mTimeCriteria & Chr(34) & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
mPositionCriteria & Chr(34) & "),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
& mQuestion1Criteria & Chr(34) & ") "

'mFormula = mFormula & mQuestion1Range.Address & ")"     'This sums
correctly

' MsgBox mFormula

Kountifs = .Evaluate(mFormula)

End With

If IsError(Kountifs) Then
MsgBox "Error in evaluating"
Else
MsgBox Kountifs
End If

21-Nov-09 04:00 AM

Hi

You miss a closing paranthesis in your formula.



Regards,
Per

21-Nov-09 08:09 AM

Thanks.  That was it.

21-Nov-09 08:35 AM

Check your other post.

In fact, if you are going to repost your question, please go back to the other
threads and say that you reposted.

That way, others will not waste their time duplicating answers.


--

Dave Peterson