<div>
some more detail would be useful, snippet of code, what you're trying to achieve, why you are using rank and in what context 
 
 

</div>

<div>
i want to rank the above list into first, second, third, No Win. Its part of a competition results.. 
 
here is my code 
 
Public Sub assignRank() 
Dim sCurrentVal 
Dim sRange As Range 
 
 
&nbsp; Set sRange = ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;I10:I29&quot;) 
&nbsp; &nbsp; 
&nbsp; For iCount = 10 To WorksheetFunction.Count(ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H10:H29&quot;)) + 9 
&nbsp; &nbsp; iRank = &quot;&quot; 
&nbsp; &nbsp; sCurrentVal = ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;I&quot; &amp;&nbsp;iCount).Value 
&nbsp; 
&nbsp; &nbsp;iRank = WorksheetFunction.Rank(sCurrentVal, sRange, 0) 
&nbsp; &nbsp; Debug.Print sCurrentVal &amp;&nbsp;&quot;,&quot; &amp;&nbsp;iRank1 
&nbsp; &nbsp; Select Case iRank 
&nbsp; &nbsp; Case 1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;J&quot; &amp;&nbsp;iCount).Value = &quot;First&quot; 
&nbsp; &nbsp; Case 2 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;J&quot; &amp;&nbsp;iCount).Value = &quot;Second&quot; 
&nbsp; &nbsp; Case 3 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;J&quot; &amp;&nbsp;iCount).Value = &quot;Third&quot; 
&nbsp; &nbsp; Case Else 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;J&quot; &amp;&nbsp;iCount).Value = &quot;No Win&quot; 
&nbsp; &nbsp; End Select 
&nbsp; &nbsp; &nbsp; 
&nbsp; Next 
 
End Sub 
 
 
 
 
 

</div>

<div>
this is more correct code.. 
 
Public Sub assignRank() 
Dim sCurrentVal 
Dim sRange As Range 
 
 
&nbsp; Set sRange = ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H10:H29&quot;) 
&nbsp; &nbsp; 
&nbsp; For iCount = 10 To WorksheetFunction.Count(ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H10:H29&quot;)) + 9 
&nbsp; &nbsp; iRank = &quot;&quot; 
&nbsp; &nbsp; sCurrentVal = ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H&quot; &amp;&nbsp;iCount).Value 
&nbsp; 
&nbsp; &nbsp;iRank = WorksheetFunction.Rank(sCurrentVal, sRange, 0) 
&nbsp; &nbsp; Debug.Print sCurrentVal &amp;&nbsp;&quot;,&quot; &amp;&nbsp;iRank1 
&nbsp; &nbsp; Select Case iRank 
&nbsp; &nbsp; Case 1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H&quot; &amp;&nbsp;iCount).Value = &quot;First&quot; 
&nbsp; &nbsp; Case 2 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H&quot; &amp;&nbsp;iCount).Value = &quot;Second&quot; 
&nbsp; &nbsp; Case 3 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H&quot; &amp;&nbsp;iCount).Value = &quot;Third&quot; 
&nbsp; &nbsp; Case Else 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H&quot; &amp;&nbsp;iCount).Value = &quot;No Win&quot; 
&nbsp; &nbsp; End Select 
&nbsp; &nbsp; &nbsp; 
&nbsp; Next 
 
End Sub
</div>

<div>
my problem is ranking &quot;Second&quot; is not available.. 
 
this is my result.. 
60 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;First 
60 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;First 
48 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Third 
36 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
36 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
36 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
36 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
36 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
36 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
30 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
24 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
24 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
12 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
12 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Win 
 
 

<pre><code class="code-10 nolinks" id="code-8224048">Public Sub assignRank()
Dim sCurrentVal
Dim sRange As Range


 Set sRange = ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H10:H29&quot;)
 
 For iCount = 10 To WorksheetFunction.Count(ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H10:H29&quot;)) + 9
 iRank = &quot;&quot;
 sCurrentVal = ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;H&quot; &amp; iCount).Value
 
 iRank = WorksheetFunction.Rank(sCurrentVal, sRange, 0)
 Debug.Print sCurrentVal &amp; &quot;,&quot; &amp; iRank1
 Select Case iRank
 Case 1
 ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;I&quot; &amp; iCount).Value = &quot;First&quot;
 Case 2
 ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;I&quot; &amp; iCount).Value = &quot;Second&quot;
 Case 3
 ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;I&quot; &amp; iCount).Value = &quot;Third&quot;
 Case Else
 ActiveWorkbook.Worksheets(&quot;18-40 Solo&quot;).Range(&quot;I&quot; &amp; iCount).Value = &quot;No Win&quot;
 End Select
 
 Next

End Sub
</code></pre>
</div>

<div>
I think you are misunderstanding the 'rank' function and iits handling of duplicate numbers, in your example the first 60 would be ranked 1, the second 60 is also ranked 1 then 48 is ranked 3 - the rank of 2 is skipped because of the duplicated 60. 
 
the only way to force it to work (as it stands) is to deduplicate the numbers first, rank the deduplicated set, and then lookup each result's rank against the resulting list.This would give 
60 First 
48 second 
36 third 
30 no win 
and so on for a result rankingand then a comparison of each result to this table woult achieve your goal. There is also the possibility to rework the function so that the duplicated results gets a correcting factor applied so the second 60 would be ranked as 1.5 and so on but this could get messy if there are more than 2 'tied' results in any 1 list. 
 
Depending on which version of excel you are using the Rank.EQ function might be what you are looking for. 
 

</div>

<div>
I have excel 2003..let me know whats the work around..How to take the duplicates out and then integerate it back with the ranking..
</div>

<div>
Its difficult to give you worked examples because I don't know how your worksheet is structured but in principle: 
 
1. Select the whole list of results 
2. Use advanced filter to create a new list of unique records - see below 
3. Use Rank function against the unique list to give a Unique Ranking List 
4. Use vlookup against the results list to find the correct rank from the Unique Ranking List 
 
If your results are in A2:A21 then give column B the same name as column A (ie: B1 = A1) and 
this will put your Unique Results in B2:Bnn 
 
Range(&quot;A1:A21&quot;).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ 
&nbsp; &nbsp; &nbsp; &nbsp;&quot;B1&quot;), Unique:=True 
 
You can now put the rankings in column C and a lookup in column D 
 
the attached sheet shows it working in a spreadsheet. 
 
To convert this to VBA code will need you to modify it to cope the various ranges of numbers (how many results, how many are unique) and decide how you want the output to look - is it acceptable to have a separate column showing rank or do you want a formatted ouput that is not connected to the results list? 
 
From an outside view I would say that with the limited information you have provided the 'Ranking' function is uneccessary and you can handle the whole thing in the spreadsheet - but there may be reasons why you need the VBA function <a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/508812/rank.xls" target="_blank" class="file-inline" title="example of ranking with unique results (33 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>rank.xls</a>
</div>

rank.xls

<div>
see the attached file 60..is twice in that 
<a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/508940/rank.xls" target="_blank" class="file-inline" title="file (38 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>rank.xls</a>
</div>

rank-1.xls

<div>
<div class="content wysiwyg-content">
see my list i want to rank in vba..rank function fails because it skips second rank.. 
 
60 
60 
48 
36 
36 
36 
36 
36 
36 
30 
24 
24 
12 
12 
0 
0 
0 
0 
0 
0 

</div>
</div>

see my list i want to rank in vba..rank function fails because it skips second rank..

60
60
48
36
36
36
36
36
36
30
24
24
12
12
0
0
0
0
0
0


how to rank  a column in vba

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.