Having RANK work with VB to convert 1 to 1st

[slam]

I had this VB code that worked great when I entered values as 1, 2, 3 etc.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Formula
Case 1: Target.Value = Target.Value & "st"
Case 2: Target.Value = Target.Value & "nd"
Case 3: Target.Value = Target.Value & "rd"
Case 4 To 16: Target.Value = Target.Value & "th"
End Select
Application.EnableEvents = True

End Sub

However, I am now slightly changing the functionality of that column so that the values are automatically entered based on the value in the adjacent column using a RANK formula.

I have this formula

=IF(ISBLANK(E2),"",RANK(E2,$E$2:$E$17,1))

Unfortunately, the values are no longer converted to 1st, 2nd, 3rd etc. Is there any way to do this? - make the formula and VB work together?

Thanks

 

[VoG]

Try Target.Value instead of Target.Formula.

 

[slam]

Try Target.Value instead of Target.Formula.

As follows?



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Value
Case 1: Target.Value = Target.Value & "st"
Case 2: Target.Value = Target.Value & "nd"
Case 3: Target.Value = Target.Value & "rd"
Case 4 To 16: Target.Value = Target.Value & "th"
End Select
Application.EnableEvents = True

End Sub




No luck unfortunately. Still leaves the result as 1 instead of 1st....

 

[VoG]

Sorry, I didn't think that through before posting (but generally you should test .Value not .Formula.

How about doing away with the VBA and modifying the formula?

=IF(ISBLANK(E2),"",RANK(E2,$E$2:$E$17,1)&LOOKUP(RANK(E2,$E$2:$E$17,1),{1,2,3,4},{"st","nd","rd","th"}))

 

[slam]

Sorry, I didn't think that through before posting (but generally you should test .Value not .Formula.

How about doing away with the VBA and modifying the formula?

=IF(ISBLANK(E2),"",RANK(E2,$E$2:$E$17,1)&LOOKUP(RANK(E2,$E$2:$E$17,1),{1,2,3,4},{"st","nd","rd","th"}))

That is excellent. I'd much rather have a formula than VB, and that works great. I trust there is some build in excel wizardry that allows it to know that 5 through 16 also ends in "th"?

 

[T. Valko]

I had this VB code that worked great when I entered values as 1, 2, 3 etc.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Formula
Case 1: Target.Value = Target.Value & "st"
Case 2: Target.Value = Target.Value & "nd"
Case 3: Target.Value = Target.Value & "rd"
Case 4 To 16: Target.Value = Target.Value & "th"
End Select
Application.EnableEvents = True

End Sub

However, I am now slightly changing the functionality of that column so that the values are automatically entered based on the value in the adjacent column using a RANK formula.

I have this formula

=IF(ISBLANK(E2),"",RANK(E2,$E$2:$E$17,1))

Unfortunately, the values are no longer converted to 1st, 2nd, 3rd etc. Is there any way to do this? - make the formula and VB work together?

Thanks

You can combine the rank and ordinal in the same formula.

Book1
	E	F
1	Value	Rank
2	96	8th
3	10	2nd
4	_	_
5	54	5th
6	81	6th
7	2	1st
8	88	7th
9	34	4th
10	22	3rd
Sheet1

This formula entered in F2 and copied down:

=IF(E2="","",RANK(E2,E$2:E$10,1)&MID("thstndrdth",MIN(9,2*RIGHT(RANK(E2,E$2:E$10,1))*(MOD(RANK(E2,E$2:E$10,1)-11,100)>2)+1),2))

 

[VoG]

Basically 4 and above will return th. That's OK as you are only going up to 16. If you were going up to 21 then the formula would need amending (and potentially get messy).