Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom function problem
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 -- Carlton ----------------------------------------------------------------------- Carlton L's Profile: http://www.excelforum.com/member.php...fo&userid=2574 View this thread: http://www.excelforum.com/showthread.php?threadid=39156 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom function problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom function problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom view problem - bug? | Excel Discussion (Misc queries) | |||
Custom Function: Detecting the cell the function is used in | Excel Programming | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |