how to? set my range= my UDF argument (range vs. value in range) [advanced?]

K

Keith R

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
 
T

Tom Ogilvy

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
 
K

Keith R

Worked like a charm- thanks Tom!
:)

Tom Ogilvy said:
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).
Click to expand...

 
Top