Dana DeLouis
12/14/2006 3:00:00 AM
> 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" <anthony.ting@gmail.com> wrote in message
news:1166058504.045560.12550@16g2000cwy.googlegroups.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
>