I didn't run your function but, since it looks as if you intend NewRng to be
a variable of type range, you have to use Set:

Set NewRng = TotRng.Resize(, ColAdj)

It's always a good idea to declare all your variables. Also it's not a good
idea to just pick up cell values from the worksheet, as you do with
Range("Month"). You won't see any Excel function do this. It should only
use values that are passed to the function. So you might add a month
argument to the function if you wanted to do this.

Another possible problem (I cannot be sure since I don't know what is going
in cell Month) is that ColAdj may be zero or negative. Resize arguments
have to be positive since they represent the number of columns or rows after
resizing, not the number to resize by.
--
Jim
"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