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

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

Thanks. That was it.

"Per Jessen" wrote:

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

.

Check your other post.

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

That way, others won't waste their time duplicating answers.

DogLover wrote:

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

--

Dave Peterson