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

I think you have a couple of choices.

The first is quick and dirty. Change all your 670's to 65536. Use the whole
column except for row 1.

The second is something like:

Option Explicit
Sub testme()

Dim myNameRange As Range
Dim myHourRange As Range
Dim myMonthRange As Range
Dim EmpName As String
Dim tempLastRow As Long
Dim tempMonth As Long
Dim n As Long
Dim j As Long
Dim myFormula As String

n = 1
j = 1
EmpName = "Mike"
tempMonth = 8

With Worksheets("Temp")
tempLastRow = .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))

End With

myFormula = "SUMPRODUCT((" & myNameRange.Address(external:=True) _
& "=""" & EmpName & """) * (" & _
myMonthRange.Address(external:=True) & "=""" & tempMonth & """)*(" & _
myHourRange.Address(external:=True) & "))"

Debug.Print myFormula

Sheets("SUM").Cells(n, j) = Evaluate(myFormula)

End Sub

(I like to be able to see how I'm building the formula, so I added that variable
and the debug.print statement.

When tested it, I got something like:

SUMPRODUCT(([book1.xls]Temp!$C$2:$C$33="Mike")
* ([book1.xls]Temp!$E$2:$E$33="8")
*([book1.xls]Temp!$D$2:$D$33))

I'm not sure what tempMonth is, but are you sure you want it in double quotes?
(I made it a Long in my testing.)





Michael wrote:

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

--

Dave Peterson

Great! Thank you so much!

"Dave Peterson" wrote:

I think you have a couple of choices.

The first is quick and dirty. Change all your 670's to 65536. Use the whole
column except for row 1.

The second is something like:

Option Explicit
Sub testme()

Dim myNameRange As Range
Dim myHourRange As Range
Dim myMonthRange As Range
Dim EmpName As String
Dim tempLastRow As Long
Dim tempMonth As Long
Dim n As Long
Dim j As Long
Dim myFormula As String

n = 1
j = 1
EmpName = "Mike"
tempMonth = 8

With Worksheets("Temp")
tempLastRow = .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))

End With

myFormula = "SUMPRODUCT((" & myNameRange.Address(external:=True) _
& "=""" & EmpName & """) * (" & _
myMonthRange.Address(external:=True) & "=""" & tempMonth & """)*(" & _
myHourRange.Address(external:=True) & "))"

Debug.Print myFormula

Sheets("SUM").Cells(n, j) = Evaluate(myFormula)

End Sub

(I like to be able to see how I'm building the formula, so I added that variable
and the debug.print statement.

When tested it, I got something like:

SUMPRODUCT(([book1.xls]Temp!$C$2:$C$33="Mike")
* ([book1.xls]Temp!$E$2:$E$33="8")
*([book1.xls]Temp!$D$2:$D$33))

I'm not sure what tempMonth is, but are you sure you want it in double quotes?
(I made it a Long in my testing.)





Michael wrote:

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

--

Dave Peterson