How to get range name inside fuction
 

Hello,

I have a function which gets 3 range parameter.
I am trying to get range names inside the function in order to use
Evaluate function.

Function cond_average(a, b, c)

x = "SumProduct(--(" & a.Address & "=" & b.Address & "), --(" &
c.Address & "< """"))"

MsgBox x

If Evaluate(x) = 0 Then
cond_average = 20
Else
cond_average = Application.SumIf(a, b, c) / Application.CountIf(a,
b)
cond_average = 1
End If

End Function

My problem is that in the message box I see that a.Address (for
example) displays only cells range but not the worksheet name. For
example:

When using: cond_average(Sheet1!A2:A5,Sheet2!A3,Sheet1!B2:B5)
a.Address is A2:A5 and not Sheet1!A2:A5
b.address is A3 and not Sheet2!A3
c.address is B2:B5 and not Sheet1!B2:B5

What is wrong ?

Thank you
Gil D.

How to get range name inside fuction
 

It should not matter:

Consider:

Function outrange(R As Range) As Double
MsgBox (R.Address)
outrange = R.Value
End Function

=outrange(Sheet2!A1:A1)
and
=outrange(Sheet1!A1:A1)
will both message $A$1

but the first call will return Sheet2's value and the second call will
return Sheet1's value

--
Gary''s Student


"Gil D." wrote:

Hello,

I have a function which gets 3 range parameter.
I am trying to get range names inside the function in order to use
Evaluate function.

Function cond_average(a, b, c)

x = "SumProduct(--(" & a.Address & "=" & b.Address & "), --(" &
c.Address & "< """"))"

MsgBox x

If Evaluate(x) = 0 Then
cond_average = 20
Else
cond_average = Application.SumIf(a, b, c) / Application.CountIf(a,
b)
cond_average = 1
End If

End Function

My problem is that in the message box I see that a.Address (for
example) displays only cells range but not the worksheet name. For
example:

When using: cond_average(Sheet1!A2:A5,Sheet2!A3,Sheet1!B2:B5)
a.Address is A2:A5 and not Sheet1!A2:A5
b.address is A3 and not Sheet2!A3
c.address is B2:B5 and not Sheet1!B2:B5

What is wrong ?

Thank you
Gil D.

How to get range name inside fuction
 

Hello,

Thank you for your help.
It solved my problem.

Gil D.

How to get range name inside fuction
 

You need to do more testing. That won't solve your problem

Function cond_average(a, b, c)

x = "SumProduct(--(" & a.Address(0,0,xlA1,True) _
& "=" & b.Address(0,0,xlA1,True) & "), --(" & _
c.Address(0,0,xlA1,True) & "< """"))"

should be what you are after.


It is true the the range reference is located on a specific sheet, but since
you are using the address property, this would not be visible to the
evaluate function unless you specified the sheet name - just like you have
to do in the worksheet.

--
Regards,
Tom Ogilvy


"Gil D." wrote in message
oups.com...
Hello,

Thank you for your help.
It solved my problem.

Gil D.