custom function problem

[Carlton L]

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

Function YTDTotal(TotRng As Range) As Integer
' "Month" is the range name of the cell that contains the numbe
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 th
worksheet.

Any help would be greatly appreciated

 

[Jim Rech]

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.

 

[Bob Phillips]

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)