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
|