Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to loop through a 2-column range argument ofnumbers in a UDF?
What is the best way to loop through a range argument in a User
Defined Function that is used n a worksheet cell when the range is a 2- column range of cells and I want to do calculations with the numbers by row for each pair of values? I need it to work in cases when the worksheet is recalculated and the active sheet may be different from the sheet that has the cell where the function is located. Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to loop through a 2-column range argument ofnumbers in a UDF?
Make sure you pass the range to the function.
Function myFunc(rng as range) as variant 'or as long or as ... Then use: =myfunc('sheet 99'!a1:b10) If you don't pass the range(s) that the function needs, then it won't know when to recalculate. Making the function volatile will mean that the function could be one full calculation behind -- so the results should not be trusted until that recalc. Function myFunc(rng as range) as variant dim myCell as range dim mySum as double mySum = 0 'some validation if rng.areas.count 1 then myfunc = "just a single area, please" exit function end if if rng.columns.count 2 then myfunc = "just two columns, please" exit function end if 'then you have a few options. 'you could loop through the cells in the first column for each mycell in rng.columns(1) if lcase(mycell.text) = lcase("hi there") then if isnumeric(mycell.offset(0,1).value) then mysum = mysum + mycell.offset(0,1).value end if end if next mycell myfunc = mysum end function (untested, uncompiled.) You could also loop through the rows, then loop through the columns. dim mycell as range dim myrow as range 'same checks for each myrow in rng.rows for each mycell in myrow.cells 'do something next mycell next myrow Greg wrote: What is the best way to loop through a range argument in a User Defined Function that is used n a worksheet cell when the range is a 2- column range of cells and I want to do calculations with the numbers by row for each pair of values? I need it to work in cases when the worksheet is recalculated and the active sheet may be different from the sheet that has the cell where the function is located. Greg -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to loop through a 2-column range argument of numbers in a UDF?
If your UDF accepts a Range object as a parameter, you can use code
like the following: Function Test(RR As Range) As Double Dim RNdx As Long Dim Result As Double For RNdx = 1 To RR.Rows.Count Result = Result + (RR(RNdx, 1) / RR(RNdx, 2)) Next RNdx Test = Result End Function This function sums the ratio of each pair of values in the rows of the range that was passed in. E.g., 1 2 3 4 5 6 =Test(A1:B4) returns 2.9583 This function will work regardless of what sheet might be active. If for some reason you need to know from which cell the function is called, Application.Caller will reference the Range from which the function was called. From that, you can get the Worksheet and Workbook if desired. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 7 Apr 2009 21:58:19 -0700 (PDT), Greg wrote: What is the best way to loop through a range argument in a User Defined Function that is used n a worksheet cell when the range is a 2- column range of cells and I want to do calculations with the numbers by row for each pair of values? I need it to work in cases when the worksheet is recalculated and the active sheet may be different from the sheet that has the cell where the function is located. Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to loop through a 2-column range argument ofnumbers in a UDF?
Chip's example worked perfectly and was exactly what I was looking for.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through single column of named range | Excel Programming | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Function (array argument, range argument, string argument) vba | Excel Programming | |||
Range as argument in function | Excel Programming | |||
Passing range as argument | Excel Programming |