VBA Dynamic Name Range (Sum)

[T-rev]

hopefully someone can help,

i have a sheet of data imported from an external source (the number of rows changes for each import)

i have 1 column which displays an employees Hours worked (eg row 4) and then various commisions (eg rows 5 - 9) for each date within the pay cycle

using vba i have seperated the commisions into another column, so now i have 1 column for "hours worked" and another column for "commisions"

i am trying to sum both of these columns which now include blanks, after searching the web i have found a way to acheive this using "DYNAMIC NAMED RANGES"

Here is what i have so far...

Range("E4:E" & Cells.End(xlDown).Row).Select
    
    ActiveWorksheet.Names.Add Name:="SumHrs", RefersTo:= _
        "=OFFSET($E$4,0,0,COUNT($E:$E),1)"
        Range("A4").Select
        Selection.End(xlDown).Select
        Selection.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=SUM(SumHrs)"

I am not sure what i am missing/doing incorrectly?

i would like it to sum the entire column from the first Numeric entry to the last, including the blanks...

Any help would be greatly appreciated

 

[niceguy_jay]

ActiveWorkbook.Names.Add Name:="SumHrs", RefersTo:= _
"=OFFSET(Sheet2!$E$4,0,0,COUNTA(Sheet2!$E:$E),1)"

Name Managers are Workbook level functions....

 

[T-rev]

excellent, that helps alot, it's at least returning a value now, the only problem is the value = 0, not the sum of my data. any idea why this might be?

also when i highlight the range, it does not define it as "Named" (SumHrs)

i checked the named range SumHrs and it refers to E4 and every cell down to the last row of data, but then it also includes the cell i am placing the formula in?
:S

 

[T-rev]

being a workbook function, do i have to place it in the workbook module? or can i place it in a standard module? if i do, how do i access it from my standard module?

 

[niceguy_jay]

being a workbook function, do i have to place it in the workbook module? or can i place it in a standard module? if i do, how do i access it from my standard module?

You can place it under any module....

 

[niceguy_jay]

excellent, that helps alot, it's at least returning a value now, the only problem is the value = 0, not the sum of my data. any idea why this might be?

also when i highlight the range, it does not define it as "Named" (SumHrs)

i checked the named range SumHrs and it refers to E4 and every cell down to the last row of data, but then it also includes the cell i am placing the formula in?
:S

does your column E after E4 contains blank cells....
surprisingly it is working fine for me and returning the sum of the numbers in it...

 

[T-rev]

Appologies, perhapse i mis-understood the definition of "Dynamic Range"

infact what i needed was more along the lines of summing "growing data"

This code was sufficient

Range("$E$4:E" & Cells.End(xlDown).Row).Name = "SumHrs"

    Range("A4").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 4).Select
        ActiveCell.Formula = "=SUM(SumHrs)"

 

[T-rev]

Appologies, perhapse i mis-understood the definition of "Dynamic Range"

infact what i needed was more along the lines of summing "growing data"

This code was sufficient

Range("$E$4:E" & Cells.End(xlDown).Row).Name = "SumHrs"

    Range("A4").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 4).Select
        ActiveCell.Formula = "=SUM(SumHrs)"

thanks for your assistance

 

[jbeaucaire]

You have to be careful defining your "dynamic range" using a function that counts values and uses that count to do an offset to create the range. You said your range includes blank cells, meaning you're in trouble already.

If you put a value in A1 and A10, that's 8 blank cells in between. The formula =COUNTA(A:A) will only give you an answer of 2, which is fine in some cases. But if you try to use that value to OFFSET from A1 to get a range, your range will be A1:A3, you'll miss the value in A10 completely.

If your range is going to include blanks cells, don't count. If the values are numeric, use this for your dynamic range determination:

=$E$4:INDEX($E:$E,MATCH(99^99,$E:$E,1))


Now, having said that, if the only thing in column E are these growing values being added to periodically, why not just use a non-dynamic formula:

=SUM(E:E)