Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF's and Worksheet Function VBA Combinations
Hello,
I'm not sure what I'm doing wrong but I hope someone can help me. I have a UDF that I want to calculate a value for me. The normal Worksheet display of the function would be =IF(ISERROR(AVERAGE(E5:E6)),"",AVERAGE(E5:E8)) I would like this to be a user defined function letting the range be selectable by the user. I've created Each individual part of the function and it works correctly but for some reason when I try each piece together it doesn't work. If you run a macro to record the steps of manually inserting the function the VB looks like. ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(AVERAGE(R[-16]C[-1]:R[-15]C[- 1])),"""",AVERAGE(R[-16]C[-1]:R[-15]C[-1]))" I've tried many ways to turn this into a function. Like I said I can take each individual function and have it work correctly in a UDF but to combine the IF, ISERROR, and AVERAGE functions and then assign this value to the Function name just doesn't seem to work. I've debugged and it has had the correct true and false values for data that I tested it on. Oh well, hopefully someone knows. I would also like to create UDF's for these functions as well. =IF(sheet!F3="N/A","N/A",IF(sheet!F3="","",NETWORKDAYS (sheet!D3,sheet!F3,sheet!D3))) =SUM(IF(sheet1!$C$2:$C$500="AA",IF(sheet1! $D$2:$D$500<=DATEVALUE("8/31/2004"),IF(sheet1! $D$2:$D$500=DATEVALUE("8/1/2004"),IF(sheet1! $B$2:$B$500="DD",1,0),0),0),0)) I've got a few more questions but I'll hold off for now. Thanks to whom ever can help me. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF's and Worksheet Function VBA Combinations
Try this
Public Function AvgWithoutErr(rngA as range) variant 'to perform like =IF(ISERROR(AVERAGE(E5:E6)),"",AVERAGE(E5:E8)) on error goto E1 AvgWithoutErr = application.worksheetfunction.average(rnga) if ISERROR(avgWithoutErr) Then AvgWithoutErr = "" exit function E1: AvgWithoutErr = "" Exit Function Stephen Rasey Houston http://wiserways.com http://excelsig.org "Matt P." wrote in message ... Hello, I'm not sure what I'm doing wrong but I hope someone can help me. I have a UDF that I want to calculate a value for me. The normal Worksheet display of the function would be =IF(ISERROR(AVERAGE(E5:E6)),"",AVERAGE(E5:E8)) I would like this to be a user defined function letting the range be selectable by the user. I've created Each individual part of the function and it works correctly but for some reason when I try each piece together it doesn't work. If you run a macro to record the steps of manually inserting the function the VB looks like. ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(AVERAGE(R[-16]C[-1]:R[-15]C[- 1])),"""",AVERAGE(R[-16]C[-1]:R[-15]C[-1]))" I've tried many ways to turn this into a function. Like I said I can take each individual function and have it work correctly in a UDF but to combine the IF, ISERROR, and AVERAGE functions and then assign this value to the Function name just doesn't seem to work. I've debugged and it has had the correct true and false values for data that I tested it on. Oh well, hopefully someone knows. I would also like to create UDF's for these functions as well. =IF(sheet!F3="N/A","N/A",IF(sheet!F3="","",NETWORKDAYS (sheet!D3,sheet!F3,sheet!D3))) =SUM(IF(sheet1!$C$2:$C$500="AA",IF(sheet1! $D$2:$D$500<=DATEVALUE("8/31/2004"),IF(sheet1! $D$2:$D$500=DATEVALUE("8/1/2004"),IF(sheet1! $B$2:$B$500="DD",1,0),0),0),0)) I've got a few more questions but I'll hold off for now. Thanks to whom ever can help me. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF's and Worksheet Function VBA Combinations
Thank you so much that just shaved off so much time.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF's and Worksheet Function VBA Combinations
Here is one for your second function:
Function IfFunction(Range1, Range2, Range3) Dim BlnSameSize As Boolean Dim dblArray() As Double On Error GoTo ErrRtn BlnSameSize = False If Range1.Cells.Count = Range2.Cells.Count And Range2.Cells.Count = Range3.Cells.Count Then BlnSameSize = True If Not BlnSameSize Then GoTo ErrRtn ReDim dblArray(Range1.Cells.Count - 1) For i = 1 To Range1.Cells.Count If Range1.Cells(i) = "AA" And Range2.Cells(i) <= #8/31/2004# And Range2.Cells(i) = #8/1/2004# And Range3.Cells(i) = "DD" Then dblArray(i - 1) = 1 Else dblArray(i - 1) = 0 End If Next i IfFunction = WorksheetFunction.Sum(dblArray) Exit Function ErrRtn: IfFunction = CVErr(xlErrValue) End Function -----Original Message----- Thank you so much that just shaved off so much time. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup plus other function/s combinations required? | Excel Worksheet Functions | |||
Permutations or Combinations or some other function?? | Excel Discussion (Misc queries) | |||
UDF's using other UDF's | Excel Worksheet Functions | |||
Function generating all possible combinations of set of numbers | Excel Worksheet Functions | |||
Keyboard Shortcuts combinations of function keys, c... | Excel Worksheet Functions |