Using Variables in VBA Function

[millhouse123]

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]

 

[erik.van.geit]

Hi,

what do you expect this code to do ?
it would be better you refered to the original code
is it the EVALUATE-line which doens't work ?

can't you continue in the original thread ?
you could PM Joe Was (isn't he the author ?)

kind regards,
Erik