How to get range name inside fuction

[Gil D.]

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.

[Gary''s Student]

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.
>
>

[Gil D.]

Hello,

Thank you for your help.
It solved my problem.

Gil D.

[Tom Ogilvy]

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." <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Thank you for your help.
> It solved my problem.
>
> Gil D.
>