Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help. Sumproduct Data Type Mismatch Erro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default Help. Sumproduct Data Type Mismatch Erro
All this code works perfectly except when I try to add specifically the field
mEntityCriteria with mEntityRange. I cannot get it to calculate correct, and
now I'm getting an Error 13 Data Type Mismatch of this line of code in
mformula.

Can anyone spot something wrong??? Please help.

Private Sub VBATEST_Click()

Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mEntityCriteria As String
Dim mStatusCriteria As String
Dim mBeginDateCriteria As Variant
Dim mEndDateCriteria As Variant
Dim mQuestion1Criteria As String
Dim Kountifs As Long

Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mEntityRange As Range
Dim mOrientMoYrRange As Range
Dim mStatusRange As Range
Dim mQuestion1Range As Range

Dim mFormula As String
Dim mBegMo As Integer, mBegYr As Integer
Dim mEndMo As Integer, mEndYr As Integer

'mPositionCriteria = mPositionC ' This line of Code allows automatic
RECALCULATION
'mEntityCriteria = mEntityC
'mBeginDateCriteria = mBeginDateC
'mEndDateCriteria = mEndDateC
'mStatusCriteria = mStatusC

' Needed if Subroutine vs Functio, change to passing variable later
mPositionCriteria = Worksheets("RFJ").Range("N6")
mEntityCriteria = Worksheets("RFJ").Range("N7")
mBeginDateCriteria = Worksheets("RFJ").Range("N8")
mEndDateCriteria = Worksheets("RFJ").Range("N9")
mStatusCriteria = Worksheets("RFJ").Range("N10")

mBegMo = Month(mBeginDateCriteria)
mBegYr = Year(mBeginDateCriteria)

If Month(mEndDateCriteria) = 12 Then
mEndMo = 1
mEndYr = Year(mBeginDateCriteria) + 1
Else
mEndMo = Month(mEndDateCriteria) + 1
mEndYr = Year(mBeginDateCriteria)
End If

' Set Criterias
mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)
mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34)

'Position Criteria
If mPositionCriteria = "<" Then
mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
End If

'Entity Criteria

If mEntityCriteria = "<" Then
mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
End If

'Status Criteria
If mStatusCriteria = "<" Then
mStatusCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
End If

'
With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mEntityRange = .Range("DataEntity")
Set mOrientMoYrRange = .Range("DataOrientMoYr")
Set mStatusRange = .Range("DataStatus")
Set mQuestion1Range = .Range("DataQuestion1")

MsgBox mPositionCriteria
MsgBox mEntityCriteria
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
"mPositionCriteria" & Chr(34) & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & "=" & Chr(34) &
"CRMC - Community Regional Medical Center" & Chr(34) & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mBeginDateCriteria & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mEndDateCriteria & "),"
mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria &
"),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address &
mQuestion1Criteria & ") )"

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

Kountifs = .Evaluate(mFormula)

End With

If IsError(Kountifs) Then
MsgBox "Error in evaluating"
End If
MsgBox Kountifs
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default Help. Sumproduct Data Type Mismatch Erro
An example of the mEntityCriteria is = "CRMC - Community Regional Medical
Center"

Has anyone every an issue with using a criteria with spaces or - in it??
My belief is it is something with the actual string causing this error
message.

"DogLover" wrote:

All this code works perfectly except when I try to add specifically the field
mEntityCriteria with mEntityRange. I cannot get it to calculate correct, and
now I'm getting an Error 13 Data Type Mismatch of this line of code in
mformula.

Can anyone spot something wrong??? Please help.

Private Sub VBATEST_Click()

Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mEntityCriteria As String
Dim mStatusCriteria As String
Dim mBeginDateCriteria As Variant
Dim mEndDateCriteria As Variant
Dim mQuestion1Criteria As String
Dim Kountifs As Long

Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mEntityRange As Range
Dim mOrientMoYrRange As Range
Dim mStatusRange As Range
Dim mQuestion1Range As Range

Dim mFormula As String
Dim mBegMo As Integer, mBegYr As Integer
Dim mEndMo As Integer, mEndYr As Integer

'mPositionCriteria = mPositionC ' This line of Code allows automatic
RECALCULATION
'mEntityCriteria = mEntityC
'mBeginDateCriteria = mBeginDateC
'mEndDateCriteria = mEndDateC
'mStatusCriteria = mStatusC

' Needed if Subroutine vs Functio, change to passing variable later
mPositionCriteria = Worksheets("RFJ").Range("N6")
mEntityCriteria = Worksheets("RFJ").Range("N7")
mBeginDateCriteria = Worksheets("RFJ").Range("N8")
mEndDateCriteria = Worksheets("RFJ").Range("N9")
mStatusCriteria = Worksheets("RFJ").Range("N10")

mBegMo = Month(mBeginDateCriteria)
mBegYr = Year(mBeginDateCriteria)

If Month(mEndDateCriteria) = 12 Then
mEndMo = 1
mEndYr = Year(mBeginDateCriteria) + 1
Else
mEndMo = Month(mEndDateCriteria) + 1
mEndYr = Year(mBeginDateCriteria)
End If

' Set Criterias
mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)
mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34)

'Position Criteria
If mPositionCriteria = "<" Then
mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
End If

'Entity Criteria

If mEntityCriteria = "<" Then
mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
End If

'Status Criteria
If mStatusCriteria = "<" Then
mStatusCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
End If

'
With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mEntityRange = .Range("DataEntity")
Set mOrientMoYrRange = .Range("DataOrientMoYr")
Set mStatusRange = .Range("DataStatus")
Set mQuestion1Range = .Range("DataQuestion1")

MsgBox mPositionCriteria
MsgBox mEntityCriteria
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
"mPositionCriteria" & Chr(34) & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & "=" & Chr(34) &
"CRMC - Community Regional Medical Center" & Chr(34) & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mBeginDateCriteria & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mEndDateCriteria & "),"
mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria &
"),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address &
mQuestion1Criteria & ") )"

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

Kountifs = .Evaluate(mFormula)

End With

If IsError(Kountifs) Then
MsgBox "Error in evaluating"
End If
MsgBox Kountifs
End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default Help. Sumproduct Data Type Mismatch Erro
IIRC, I've had issues with the - ... apparently, there is more than one and
it's tough to spot. Don't know if that's related.
--
HTH,

Barb Reinhardt



"DogLover" wrote:

An example of the mEntityCriteria is = "CRMC - Community Regional Medical
Center"

Has anyone every an issue with using a criteria with spaces or - in it??
My belief is it is something with the actual string causing this error
message.

"DogLover" wrote:

All this code works perfectly except when I try to add specifically the field
mEntityCriteria with mEntityRange. I cannot get it to calculate correct, and
now I'm getting an Error 13 Data Type Mismatch of this line of code in
mformula.

Can anyone spot something wrong??? Please help.

Private Sub VBATEST_Click()

Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mEntityCriteria As String
Dim mStatusCriteria As String
Dim mBeginDateCriteria As Variant
Dim mEndDateCriteria As Variant
Dim mQuestion1Criteria As String
Dim Kountifs As Long

Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mEntityRange As Range
Dim mOrientMoYrRange As Range
Dim mStatusRange As Range
Dim mQuestion1Range As Range

Dim mFormula As String
Dim mBegMo As Integer, mBegYr As Integer
Dim mEndMo As Integer, mEndYr As Integer

'mPositionCriteria = mPositionC ' This line of Code allows automatic
RECALCULATION
'mEntityCriteria = mEntityC
'mBeginDateCriteria = mBeginDateC
'mEndDateCriteria = mEndDateC
'mStatusCriteria = mStatusC

' Needed if Subroutine vs Functio, change to passing variable later
mPositionCriteria = Worksheets("RFJ").Range("N6")
mEntityCriteria = Worksheets("RFJ").Range("N7")
mBeginDateCriteria = Worksheets("RFJ").Range("N8")
mEndDateCriteria = Worksheets("RFJ").Range("N9")
mStatusCriteria = Worksheets("RFJ").Range("N10")

mBegMo = Month(mBeginDateCriteria)
mBegYr = Year(mBeginDateCriteria)

If Month(mEndDateCriteria) = 12 Then
mEndMo = 1
mEndYr = Year(mBeginDateCriteria) + 1
Else
mEndMo = Month(mEndDateCriteria) + 1
mEndYr = Year(mBeginDateCriteria)
End If

' Set Criterias
mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)
mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34)

'Position Criteria
If mPositionCriteria = "<" Then
mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
End If

'Entity Criteria

If mEntityCriteria = "<" Then
mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
End If

'Status Criteria
If mStatusCriteria = "<" Then
mStatusCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
End If

'
With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mEntityRange = .Range("DataEntity")
Set mOrientMoYrRange = .Range("DataOrientMoYr")
Set mStatusRange = .Range("DataStatus")
Set mQuestion1Range = .Range("DataQuestion1")

MsgBox mPositionCriteria
MsgBox mEntityCriteria
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
"mPositionCriteria" & Chr(34) & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & "=" & Chr(34) &
"CRMC - Community Regional Medical Center" & Chr(34) & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mBeginDateCriteria & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mEndDateCriteria & "),"
mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria &
"),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address &
mQuestion1Criteria & ") )"

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

Kountifs = .Evaluate(mFormula)

End With

If IsError(Kountifs) Then
MsgBox "Error in evaluating"
End If
MsgBox Kountifs
End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Help. Sumproduct Data Type Mismatch Erro
You have an active thread elsewhere.

Please don't multipost.

DogLover wrote:

All this code works perfectly except when I try to add specifically the field
mEntityCriteria with mEntityRange. I cannot get it to calculate correct, and
now I'm getting an Error 13 Data Type Mismatch of this line of code in
mformula.

Can anyone spot something wrong??? Please help.

Private Sub VBATEST_Click()

Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mEntityCriteria As String
Dim mStatusCriteria As String
Dim mBeginDateCriteria As Variant
Dim mEndDateCriteria As Variant
Dim mQuestion1Criteria As String
Dim Kountifs As Long

Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mEntityRange As Range
Dim mOrientMoYrRange As Range
Dim mStatusRange As Range
Dim mQuestion1Range As Range

Dim mFormula As String
Dim mBegMo As Integer, mBegYr As Integer
Dim mEndMo As Integer, mEndYr As Integer

'mPositionCriteria = mPositionC ' This line of Code allows automatic
RECALCULATION
'mEntityCriteria = mEntityC
'mBeginDateCriteria = mBeginDateC
'mEndDateCriteria = mEndDateC
'mStatusCriteria = mStatusC

' Needed if Subroutine vs Functio, change to passing variable later
mPositionCriteria = Worksheets("RFJ").Range("N6")
mEntityCriteria = Worksheets("RFJ").Range("N7")
mBeginDateCriteria = Worksheets("RFJ").Range("N8")
mEndDateCriteria = Worksheets("RFJ").Range("N9")
mStatusCriteria = Worksheets("RFJ").Range("N10")

mBegMo = Month(mBeginDateCriteria)
mBegYr = Year(mBeginDateCriteria)

If Month(mEndDateCriteria) = 12 Then
mEndMo = 1
mEndYr = Year(mBeginDateCriteria) + 1
Else
mEndMo = Month(mEndDateCriteria) + 1
mEndYr = Year(mBeginDateCriteria)
End If

' Set Criterias
mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)
mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34)

'Position Criteria
If mPositionCriteria = "<" Then
mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
End If

'Entity Criteria

If mEntityCriteria = "<" Then
mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
End If

'Status Criteria
If mStatusCriteria = "<" Then
mStatusCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
End If

'
With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mEntityRange = .Range("DataEntity")
Set mOrientMoYrRange = .Range("DataOrientMoYr")
Set mStatusRange = .Range("DataStatus")
Set mQuestion1Range = .Range("DataQuestion1")

MsgBox mPositionCriteria
MsgBox mEntityCriteria
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
"mPositionCriteria" & Chr(34) & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & "=" & Chr(34) &
"CRMC - Community Regional Medical Center" & Chr(34) & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mBeginDateCriteria & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mEndDateCriteria & "),"
mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria &
"),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address &
mQuestion1Criteria & ") )"

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

Kountifs = .Evaluate(mFormula)

End With

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

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct in VBA Type Mismatch problem DogLover Excel Programming 12 November 21st 09 01:33 PM
data type mismatch error Daniel Excel Programming 4 December 22nd 07 12:32 AM
ucase 'data type mismatch' DKY[_120_] Excel Programming 9 July 28th 06 01:46 AM
Data with starting with - causes Type Mismatch icdoo[_4_] Excel Programming 3 December 31st 05 03:40 PM
Varient Int data type mismatch Kevin Excel Programming 0 August 12th 03 03:51 AM


All times are GMT +1. The time now is 08:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement