Using Relative Reference Function to apply to Range

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

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
 
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!
 
Select Case strMonth
Case Is = 38718: intMonthCount = 1
Case Is = 38749: intMonthCount = 2
Click to expand...

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
 
Back
Top