Not clear about the logic, but does this help

Function YTDTotal(TotRng As Range) As Integer
' "Month" is the range name of the cell that contains the number for the
month.
MthNum = Range("Month").Value
NewRng = TotRng(1, 1).Resize(, MthNum)
YTDTotal = WorksheetFunction.Sum(NewRng)
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Carlton L" wrote
in message ...

I have a worksheet that looks up data in a table based on a desired
month number entered in a cell with a range name of "Month." The
worksheet also looks up the YTD row totals for selected month from the
table. Without making the YTD formulas in the table a mess of nested
IF statements, I am attemting to write a custom function to include in
the YTD total only the months through the entered month number. I am
new to VBA and wrote the following custom function (which does not
work):

Function YTDTotal(TotRng As Range) As Integer
' "Month" is the range name of the cell that contains the number
for the month.
MthNum = Range("Month").Value
ColAdj = MthNum - 12
NewRng = TotRng.Resize(, ColAdj)
YTDTotal = WorksheetFunction.Sum(NewRng)
End Function

Why does this not work? I get a #Value! error when I use it on the
worksheet.

Any help would be greatly appreciated.


--
Carlton L
------------------------------------------------------------------------
Carlton L's Profile:
http://www.excelforum.com/member.php...o&userid=25745
View this thread: http://www.excelforum.com/showthread...hreadid=391562