Formula to UDF - String Data

[Juggler_IN]

Hi,

I have a formula to Rank data, say in Range("A1:A5"). The formula is: <a1) countif($a$1:$a$5,$a$1:$a$5&""))+1<a1)="" countif($a$1:$a$5,$a$1:$a$5&""))+1[="" code]
<a1) countif($a$1:$a$5,$a$1:$a$5&""))+1[="" code]
<a1) countif($a$1:$a$5,$a$1:$a$5&""))+1
SUMPRODUCT(( $A$1:$A$5 < A1 ) / COUNTIF( $A$1:$A$5 , $A$1:$A$5&"" )) + 1
<a1) countif($a$1:$a$5,$a$1:$a$5&""))+1
Output, for a sample data in A1 to A5, is:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Apple[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

I tried to convert it into a UDF but I am not getting an executable UDF. The UDF is:

Public Function RS(ByVal rngItem As Range, ByRef rngData As Range) As Double
    
    With Application.WorksheetFunction
            RS = .SumProduct((rngData < rngItem) / .CountIf(rngData, rngData & "")) + 1
    End With


End Function

What am I missing here?</a1)></a1)></a1)></a1)>

 

[RoryA]

You can't compare a multi-cell range to a value like that, or append text to one, in VBA. Why do you want it as a UDF?

 

[Juggler_IN]

@RoryA;

I need it to be a UDF. The project needs it to be implemented as UDF.

Is it doable as a UDF? I have implemented .CountIf in VBA, but not .SumProduct.

 

[Marcelo Branco]

If you really need a UDF...

Public Function RS(rngItem As Range, rngData As Range) As Double
    Dim strFor As String
    
    strFor = Replace("=SumProduct((@" & "<" & rngItem.Address & ")/CountIf(@,@&""""))+1", "@", rngData.Address)
    RS = Evaluate(strFor)
End Function

Hope this helps

M.

 

[Juggler_IN]

@Marcelo Branco;
Thx. UDF worked.

 

[Juggler_IN]

@Marcelo Branco;

One issue, if the values of the inputs are changing (with F9) the UDF output is not changing. It stays at the first result shown on entry of formula. It continues to be the same till I do F2 on each cell.

Any reason for this behavior?

 

[Marcelo Branco]

I don't understand what you mean by "changing (with F9)"

Changing manually worked for me


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Fruit​

[/td][td]

Rank​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Apple​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Banana​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Orange​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Mango​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Apple​

[/td][td]

1​

[/td][/tr]
[/table]

Formula in B2 copied down
=RS(A2,A$2:A$6)

Then i changed A4 to Avocado


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Fruit​

[/td][td]

Rank​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Apple​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Banana​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Avocado​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Mango​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Apple​

[/td][td]

1​

[/td][/tr]
[/table]

M.

 

[Juggler_IN]

What I meant is if you change only one cell in col. A then the others don't get updated on their own. I have to do f2 Enter for the balance cells in col. B to get new ranks.

 

[Rick Rothstein]

What I meant is if you change only one cell in col. A then the others don't get updated on their own. I have to do f2 Enter for the balance cells in col. B to get new ranks.

Does adding Application.Volatile to the beginning of your UDF make it work the way you want?

 

[RoryA]

Your UDF takes all its ranges in as direct arguments so shouldn't need to be made volatile. Do you have calculation set to manual?