Hey guys.

I am writing a script to go through an excel file and examine/analysize the
data. I have the script working when I hard code in the range, but I need to
make it more modular so that I do not have to change it every time the size
of the sheets change.

Here is what I have:

Sheets("SUM").cells(n,j) = Evaluate("=SUMPRODUCT(($C$2:$C$670=""" & empName
& """) * ($E$2:$E$670=""" & tempMonth & """)*($D$2:$D$670))")

This is what I want it to be like:

Dim myNameRange As Range
Dim myHourRange As Range
Dim myMonthRange As Range

Dim tempLastRow
tempLastRow = Sheets("TEMP").Cells(Rows.Count, "A").End(xlUp).Row

Set myNameRange = Range(Cells(2, 3), Cells(tempLastRow, 3))
Set myHourRange = Range(Cells(2, 4), Cells(tempLastRow, 4))
Set myMonthRange = Range(Cells(2, 5), Cells(tempLastRow, 5))

Sheets("SUM").cells(n,j) = Evaluate("=SUMPRODUCT((myNameRange=""" & empName
& """) * (myMonthRange=""" & tempMonth & """)*(myHourRange))")

I cannot make this work. How do I make the excel function recognize the
ranges?

I greatly appreciate any assistance you could provide.

Thanks,

-Michael