millhouse123
Active Member
- Joined
- Aug 22, 2006
- Messages
- 335
Last week I got help writing this SUMPRODUCT funtion with constants as the comparison but I now want to put variables in their place. I have a feeling my problem has to do with the quotes but no matter what I try I can't get it to work Perhaps somone can give me an idea. Below is the code.
The REPT_TYPE and ACCTNUM variables I am having trouble with.
Thanks
This is just the applicable code....
Sub Joe()
Worksheets("RESULTS").Activate ' Setup second worksheet to received calculated results
Range("A2").Activate ' Set initial cell to dump data
ACCTNUM = ActiveCell.Value
Do Until ActiveCell.Value = "" ' Keep going until last account has been processed.
Worksheets("DATA").Activate ' This statement required for next one to run properly
FINROW = Range("A65536").End(xlUp).Row
CELLRANGE = "$A$2:" & "$A" & "$" & FINROW: Range(CELLRANGE).Name = "ACCT_NUMB_RANGE"
CELLRANGE = "$P$2:" & "$P" & "$" & FINROW: Range(CELLRANGE).Name = "REPT_TYPE_RANGE"
CELLRANGE = "$J$2:" & "$J" & "$" & FINROW: Range(CELLRANGE).Name = "MKT_VAL_RANGE"
Dim REPT_TYPE_Name(1 To 5) As String
Dim DATA_VALUE(1 To 30) As Long
Dim CALCRESULT As Variant
REPT_TYPE_Name(1) = "Cash Equivalents"
REPT_TYPE_Name(2) = "Fixed"
REPT_TYPE_Name(3) = "Equity"
REPT_TYPE_Name(4) = "Other"
For REPT_TYPE = 1 To 4
CALCRESULT = Evaluate("=sumproduct(--(" & ACCT_NUMB_RANGE.Address & " = ""ACCTNUM""), --(" & REPT_TYPE_RANGE.Address & " = ""REPT_TYPE_Name(REPT_TYPE[/b])""), " & MKT_VAL_RANGE.Address & ")")
Next REPT_TYPE
MsgBox REPT_TYPE_Name(1)
Loop
End Sub[/code]
The REPT_TYPE and ACCTNUM variables I am having trouble with.
Thanks
This is just the applicable code....
Sub Joe()
Worksheets("RESULTS").Activate ' Setup second worksheet to received calculated results
Range("A2").Activate ' Set initial cell to dump data
ACCTNUM = ActiveCell.Value
Do Until ActiveCell.Value = "" ' Keep going until last account has been processed.
Worksheets("DATA").Activate ' This statement required for next one to run properly
FINROW = Range("A65536").End(xlUp).Row
CELLRANGE = "$A$2:" & "$A" & "$" & FINROW: Range(CELLRANGE).Name = "ACCT_NUMB_RANGE"
CELLRANGE = "$P$2:" & "$P" & "$" & FINROW: Range(CELLRANGE).Name = "REPT_TYPE_RANGE"
CELLRANGE = "$J$2:" & "$J" & "$" & FINROW: Range(CELLRANGE).Name = "MKT_VAL_RANGE"
Dim REPT_TYPE_Name(1 To 5) As String
Dim DATA_VALUE(1 To 30) As Long
Dim CALCRESULT As Variant
REPT_TYPE_Name(1) = "Cash Equivalents"
REPT_TYPE_Name(2) = "Fixed"
REPT_TYPE_Name(3) = "Equity"
REPT_TYPE_Name(4) = "Other"
For REPT_TYPE = 1 To 4
CALCRESULT = Evaluate("=sumproduct(--(" & ACCT_NUMB_RANGE.Address & " = ""ACCTNUM""), --(" & REPT_TYPE_RANGE.Address & " = ""REPT_TYPE_Name(REPT_TYPE[/b])""), " & MKT_VAL_RANGE.Address & ")")
Next REPT_TYPE
MsgBox REPT_TYPE_Name(1)
Loop
End Sub[/code]