Hi

You miss a closing paranthesis in your formula.

mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<" & Chr(34)
& mQuestion1Criteria & Chr(34) & ")" ' <=== Two closing paranthesis needed
here.


Regards,
Per



"DogLover" skrev i meddelelsen
...
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've 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