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
Bookmarks