+ Reply to Thread
Results 1 to 3 of 3

Using a range variable inside a excel function

  1. #1
    Michael
    Guest

    Using a range variable inside a excel function

    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

  2. #2
    Dave Peterson
    Guest

    Re: Using a range variable inside a excel function

    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

  3. #3
    Michael
    Guest

    Re: Using a range variable inside a excel function

    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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1