| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Relative Reference Function to apply to Range
All,
I've written a function that looks at a range of cells and adds them up based on the Offset function. The range of cells will change based on another defined range's contents. What I want to be able to do is copy the function down several rows and have that same range added via a relative reference however with the code attached, all the cells that have the formula get updated to the last cell that where the function was applied. I understand why my logic isn't right but does anyone have an idea how to make this work? Thanks for the input. -------------- Function YTDMonths() Dim intMonthCount As Integer, b As Integer, c As String Dim TempSum As Double Dim aRange ActiveCell.Select 'Determines how far into the range to add (based on month in another defined range) Select Case Range("CurrentMonth") Case Is = 38718: intMonthCount = 11 Case Is = 38749: intMonthCount = 12 Case Is = 38777: intMonthCount = 13 Case Is = 38808: intMonthCount = 14 Case Is = 38838: intMonthCount = 15 Case Is = 38869: intMonthCount = 16 Case Is = 38899: intMonthCount = 17 Case Is = 38930: intMonthCount = 18 Case Is = 38961: intMonthCount = 19 Case Is = 38991: intMonthCount = 20 Case Is = 39022: intMonthCount = 21 Case Is = 39052: intMonthCount = 22 End Select TempSum = 0 'c = ActiveCell.Address 'Debug.Print c For b = 11 To intMonthCount TempSum = TempSum + ActiveCell.Offset(0, b).Value Next YTDMonths = TempSum Calculate End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Relative Reference Function to apply to Range
Ok, I've reworked the code so that it works but it's pretty slow: Any
Suggestions? strMonth is a Date and rngRange is a 1x12 range with data in it. Function YTDMonths(strMonth, rngRange) Dim intMonthCount As Integer, b As Integer, c As String Dim TempSum As Double Dim aRange As Variant aRange = rngRange Select Case strMonth Case Is = 38718: intMonthCount = 1 Case Is = 38749: intMonthCount = 2 Case Is = 38777: intMonthCount = 3 Case Is = 38808: intMonthCount = 4 Case Is = 38838: intMonthCount = 5 Case Is = 38869: intMonthCount = 6 Case Is = 38899: intMonthCount = 7 Case Is = 38930: intMonthCount = 8 Case Is = 38961: intMonthCount = 9 Case Is = 38991: intMonthCount = 10 Case Is = 39022: intMonthCount = 11 Case Is = 39052: intMonthCount = 12 End Select TempSum = 0 'c = ActiveCell.Address 'Debug.Print c ' For b = 11 To intMonthCount ' TempSum = TempSum + ActiveCell.Offset(0, b).Value ' Next For b = 1 To intMonthCount TempSum = TempSum + aRange(1, b) Next YTDMonths = TempSum Calculate End Function Thank you! Anthony wrote: All, I've written a function that looks at a range of cells and adds them up based on the Offset function. The range of cells will change based on another defined range's contents. What I want to be able to do is copy the function down several rows and have that same range added via a relative reference however with the code attached, all the cells that have the formula get updated to the last cell that where the function was applied. I understand why my logic isn't right but does anyone have an idea how to make this work? Thanks for the input. -------------- Function YTDMonths() Dim intMonthCount As Integer, b As Integer, c As String Dim TempSum As Double Dim aRange ActiveCell.Select 'Determines how far into the range to add (based on month in another defined range) Select Case Range("CurrentMonth") Case Is = 38718: intMonthCount = 11 Case Is = 38749: intMonthCount = 12 Case Is = 38777: intMonthCount = 13 Case Is = 38808: intMonthCount = 14 Case Is = 38838: intMonthCount = 15 Case Is = 38869: intMonthCount = 16 Case Is = 38899: intMonthCount = 17 Case Is = 38930: intMonthCount = 18 Case Is = 38961: intMonthCount = 19 Case Is = 38991: intMonthCount = 20 Case Is = 39022: intMonthCount = 21 Case Is = 39052: intMonthCount = 22 End Select TempSum = 0 'c = ActiveCell.Address 'Debug.Print c For b = 11 To intMonthCount TempSum = TempSum + ActiveCell.Offset(0, b).Value Next YTDMonths = TempSum Calculate End Function |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Relative Reference Function to apply to Range
Select Case strMonth
Case Is = 38718: intMonthCount = 1 Case Is = 38749: intMonthCount = 2 Hi. Your numbers appear to be the first day of a month. Not exact, but would any ideas here help? Sub Demo() Dim intMonthCount As Integer Dim YTDMonths As Double 'Example... intMonthCount = Month(38718) YTDMonths = WorksheetFunction.Sum _ (ActiveCell.Resize(1, intMonthCount)) End Sub -- HTH :) Dana DeLouis Windows XP & Office 2003 "Anthony" wrote in message ps.com... Ok, I've reworked the code so that it works but it's pretty slow: Any Suggestions? strMonth is a Date and rngRange is a 1x12 range with data in it. Function YTDMonths(strMonth, rngRange) Dim intMonthCount As Integer, b As Integer, c As String Dim TempSum As Double Dim aRange As Variant aRange = rngRange Select Case strMonth Case Is = 38718: intMonthCount = 1 Case Is = 38749: intMonthCount = 2 Case Is = 38777: intMonthCount = 3 Case Is = 38808: intMonthCount = 4 Case Is = 38838: intMonthCount = 5 Case Is = 38869: intMonthCount = 6 Case Is = 38899: intMonthCount = 7 Case Is = 38930: intMonthCount = 8 Case Is = 38961: intMonthCount = 9 Case Is = 38991: intMonthCount = 10 Case Is = 39022: intMonthCount = 11 Case Is = 39052: intMonthCount = 12 End Select TempSum = 0 'c = ActiveCell.Address 'Debug.Print c ' For b = 11 To intMonthCount ' TempSum = TempSum + ActiveCell.Offset(0, b).Value ' Next For b = 1 To intMonthCount TempSum = TempSum + aRange(1, b) Next YTDMonths = TempSum Calculate End Function Thank you! Anthony wrote: All, I've written a function that looks at a range of cells and adds them up based on the Offset function. The range of cells will change based on another defined range's contents. What I want to be able to do is copy the function down several rows and have that same range added via a relative reference however with the code attached, all the cells that have the formula get updated to the last cell that where the function was applied. I understand why my logic isn't right but does anyone have an idea how to make this work? Thanks for the input. -------------- Function YTDMonths() Dim intMonthCount As Integer, b As Integer, c As String Dim TempSum As Double Dim aRange ActiveCell.Select 'Determines how far into the range to add (based on month in another defined range) Select Case Range("CurrentMonth") Case Is = 38718: intMonthCount = 11 Case Is = 38749: intMonthCount = 12 Case Is = 38777: intMonthCount = 13 Case Is = 38808: intMonthCount = 14 Case Is = 38838: intMonthCount = 15 Case Is = 38869: intMonthCount = 16 Case Is = 38899: intMonthCount = 17 Case Is = 38930: intMonthCount = 18 Case Is = 38961: intMonthCount = 19 Case Is = 38991: intMonthCount = 20 Case Is = 39022: intMonthCount = 21 Case Is = 39052: intMonthCount = 22 End Select TempSum = 0 'c = ActiveCell.Address 'Debug.Print c For b = 11 To intMonthCount TempSum = TempSum + ActiveCell.Offset(0, b).Value Next YTDMonths = TempSum Calculate End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative Range Reference in a sumifs formula | Excel Worksheet Functions | |||
| Range of cells: Convert relative reference into absolute | Excel Discussion (Misc queries) | |||
| Range.FormulaArray drops relative reference ? | Excel Programming | |||
| Indirect function - relative reference | Excel Programming | |||
| How to apply a function across a range of cells | Excel Programming | |||
![Anthony[_14_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
