Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to? set my range= my UDF argument (range vs. value in range) [advanced?]
I have two UDFs- the first one returns the expected value (so I assumed it
was working), but then I modified it to create a second UDF (which doesn't work) and I think they both have the same problem- I'm passing single cell ranges as arguments to my UDFs, but when I try to set a temporary range (ws) equal to the range that was passed as an argument, it sets the new range (ws) to the value of the argument's range, rather than the range itself. The two UDFs are listed below, with comments inserted to clarify my problem. If anyone can tell me how to set the ws, ws1, and ws2 ranges in code to the arguments of the UDF, I'd really appreciate it! Many thanks, Keith R XL97 This one returns the desired value, although is still sets ws to the value of the cells, not the range (afaik) ============================================= Public Function ConsolidateMe(SC1 As Range, SC2 As Range, SC3 As Range, _ SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _ SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _ SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _ SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range) As Variant 'each input range will be a single cell Dim i As Integer Dim ws As Range Dim TotalValue As Double Dim DivCount As Double For i = 1 To 19 Set ws = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7, SC8, SC9, SC10, SC11, SC12, SC13, SC14, SC15, SC16, SC17, SC18, SC19)) 'Once I debugged, I realized that this is still passing the '_value_ of SC1, SC2, etc (I think) because when I pause 'code execution and mouseover, ws shows the value in 'the SC range that is currently being looped If Not IsError(ws.Value) Then If Not IsEmpty(ws.Value) Then If IsNumeric(ws.Value) Then TotalValue = TotalValue + ws.Value DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then ConsolidateMe = TotalValue '/ DivCount Else ConsolidateMe = CVErr(xlErrNA) End If End Function ============================================= Here is the second function, which fails at the offset command, presumably because it is trying to offset from a value instead of from the range? ============================================= Public Function WeightedConsolidateMe(SC1 As Range, SC2 As Range, SC3 As Range, _ SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _ SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _ SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _ SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range, _ NumRowShift As Integer) Dim ws1 As Range Dim ws2 As Range Dim NumVal As Variant Dim DenomVal As Variant Dim TotalNum As Variant Dim TotalDenom As Variant Dim DivCount As Variant For i = 1 To 19 Set ws1 = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7, SC8, SC9, SC10, SC11, SC12, SC13, SC14, SC15, SC16, SC17, SC18, SC19)) If Not IsError(ws1.Value) Then If Not IsEmpty(ws1.Value) Then If IsNumeric(ws1.Value) Then NumVal = ws1.Value Set ws2 = Application.WorksheetFunction.Offset(ws1, 0, NumRowShift) 'it just stops here- no error or anything- it just stops.... TempRow = Application.WorksheetFunction.Row(ws2) tempcheck = TempRow Mod 50 Do Until tempcheck = 5 If Not IsError(ws2.Value) Then If Not IsEmpty(ws2.Value) Then If IsNumeric(ws2.Value) Then DenomVal = ws2.Value tempcheck = 5 Exit Do End If End If End If tempcheck = tempcheck - 1 Set ws2 = Application.WorksheetFunction.Offset(ws2, -1, 0) Loop TotalNum = TotalNum + NumVal TotalDenom = TotalDenom + DenomVal DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then WeightedConsolidateMe = TotalNum / TotalDenom Else WeightedConsolidateMe = CVErr(xlErrNA) End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to? set my range= my UDF argument (range vs. value in range) [advanced?]
I think you are misinterpreting what is being returned in your choose
function. It returns a range for me (you would get an error on the set statement if it didn't). Anyway, with this adjustment to your second function, it worked for me (I use the vba offset rather than worksheet). Public Function WeightedConsolidateMe(SC1 As Range, SC2 As Range, _ SC3 As Range, _ SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _ SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _ SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _ SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range, _ NumRowShift As Integer) Dim ws1 As Range Dim ws2 As Range Dim NumVal As Variant Dim DenomVal As Variant Dim TotalNum As Variant Dim TotalDenom As Variant Dim DivCount As Variant For i = 1 To 19 Set ws1 = (Choose(i, SC1, SC2, SC3, SC4, _ SC5, SC6, SC7, SC8, SC9, _ SC10, SC11, SC12, SC13, SC14, _ SC15, SC16, SC17, SC18, SC19)) If Not IsError(ws1.Value) Then If Not IsEmpty(ws1.Value) Then If IsNumeric(ws1.Value) Then NumVal = ws1.Value Set ws2 = ws1.Offset( _ 0, NumRowShift) TempRow = ws2.Row tempcheck = TempRow Mod 50 Do Until tempcheck = 5 If Not IsError(ws2.Value) Then If Not IsEmpty(ws2.Value) Then If IsNumeric(ws2.Value) Then DenomVal = ws2.Value tempcheck = 5 Exit Do End If End If End If tempcheck = tempcheck - 1 Set ws2 = ws2.Offset(-1, 0) Loop TotalNum = TotalNum + NumVal TotalDenom = TotalDenom + DenomVal DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then WeightedConsolidateMe = TotalNum / TotalDenom Else WeightedConsolidateMe = CVErr(xlErrNA) End If End Function -- Regards, Tom Ogilvy "Keith R" wrote in message news:01c3600b$fc0fb360$476a1bac@PC12001... I have two UDFs- the first one returns the expected value (so I assumed it was working), but then I modified it to create a second UDF (which doesn't work) and I think they both have the same problem- I'm passing single cell ranges as arguments to my UDFs, but when I try to set a temporary range (ws) equal to the range that was passed as an argument, it sets the new range (ws) to the value of the argument's range, rather than the range itself. The two UDFs are listed below, with comments inserted to clarify my problem. If anyone can tell me how to set the ws, ws1, and ws2 ranges in code to the arguments of the UDF, I'd really appreciate it! Many thanks, Keith R XL97 This one returns the desired value, although is still sets ws to the value of the cells, not the range (afaik) ============================================= Public Function ConsolidateMe(SC1 As Range, SC2 As Range, SC3 As Range, _ SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _ SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _ SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _ SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range) As Variant 'each input range will be a single cell Dim i As Integer Dim ws As Range Dim TotalValue As Double Dim DivCount As Double For i = 1 To 19 Set ws = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7, SC8, SC9, SC10, SC11, SC12, SC13, SC14, SC15, SC16, SC17, SC18, SC19)) 'Once I debugged, I realized that this is still passing the '_value_ of SC1, SC2, etc (I think) because when I pause 'code execution and mouseover, ws shows the value in 'the SC range that is currently being looped If Not IsError(ws.Value) Then If Not IsEmpty(ws.Value) Then If IsNumeric(ws.Value) Then TotalValue = TotalValue + ws.Value DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then ConsolidateMe = TotalValue '/ DivCount Else ConsolidateMe = CVErr(xlErrNA) End If End Function ============================================= Here is the second function, which fails at the offset command, presumably because it is trying to offset from a value instead of from the range? ============================================= Public Function WeightedConsolidateMe(SC1 As Range, SC2 As Range, SC3 As Range, _ SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _ SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _ SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _ SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range, _ NumRowShift As Integer) Dim ws1 As Range Dim ws2 As Range Dim NumVal As Variant Dim DenomVal As Variant Dim TotalNum As Variant Dim TotalDenom As Variant Dim DivCount As Variant For i = 1 To 19 Set ws1 = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7, SC8, SC9, SC10, SC11, SC12, SC13, SC14, SC15, SC16, SC17, SC18, SC19)) If Not IsError(ws1.Value) Then If Not IsEmpty(ws1.Value) Then If IsNumeric(ws1.Value) Then NumVal = ws1.Value Set ws2 = Application.WorksheetFunction.Offset(ws1, 0, NumRowShift) 'it just stops here- no error or anything- it just stops.... TempRow = Application.WorksheetFunction.Row(ws2) tempcheck = TempRow Mod 50 Do Until tempcheck = 5 If Not IsError(ws2.Value) Then If Not IsEmpty(ws2.Value) Then If IsNumeric(ws2.Value) Then DenomVal = ws2.Value tempcheck = 5 Exit Do End If End If End If tempcheck = tempcheck - 1 Set ws2 = Application.WorksheetFunction.Offset(ws2, -1, 0) Loop TotalNum = TotalNum + NumVal TotalDenom = TotalDenom + DenomVal DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then WeightedConsolidateMe = TotalNum / TotalDenom Else WeightedConsolidateMe = CVErr(xlErrNA) End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to? set my range= my UDF argument (range vs. value in range) [advanced?]
Worked like a charm- thanks Tom!
:) Tom Ogilvy wrote in article ... I think you are misinterpreting what is being returned in your choose function. It returns a range for me (you would get an error on the set statement if it didn't). Anyway, with this adjustment to your second function, it worked for me (I use the vba offset rather than worksheet). <snip -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Which argument can be replaced by a Tag rather than a cell range? | Excel Discussion (Misc queries) | |||
VLOOKUP with a Named Range argument | Excel Worksheet Functions | |||
countif argument for 3 occurences of which 1 refers to a range | Excel Discussion (Misc queries) |