Sort working OK but adjacent value column not

[zeekmcphee]

Hi all,The code below sorts the figures out in each block of rows then places the position of that figure in the adjacent column,like this where the smallest number is 1 second smallest 2 and so on.
CI CJ
1 24
2 26
3 29
4 34
5 35
6 37




<code>Sub rankandSort()

Dim Rng As Range
Dim Ar As Areas

Set Ar = Range("A2", Range("I" & Rows.Count).End(xlDown)).SpecialCells(xlConstants).Areas
Columns(9).Insert
Range("I1").Value = "Position"

For Each Rng In Ar
Rng.Sort key1:=Range("J:J"), order1:=xlAscending 'added was :=xlAscending
With Intersect(Rng, Range("I:I"))
.FormulaArray = "=rank(" & .Offset(, 1).Address & "," & .Offset(, 1).Address & ",1)"
.Value = .Value
End With
Next Rng

End Sub</code>
the problem arises when there are identical numbers(two or more in col j).In the following eg there are three identical
numbers in3, 4 and 5.And the code does a good job of placing them as all 3rd in position,but then instead of the final cell(184) being 4th it presents as 6th.Likewise if cells 1,2,3 were identical cell 4 would then present itself as 4th as opposed to 2nd,and so on.
1 143.0
2 144.0
3 145.0
3 145.0
3 145.0
6 184.0
does anyone have any suggestion as to modify the code to ensure that the cells values in the J cells are correctly reflected in th I cells adjacent
Kind Regards
zm

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...working-ok-but-adjacent-value-column-not.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[zeekmcphee]

Hi Fluff,
Thanks for the reply,I have put this question up on a few forums and If I get an answer to the prob I will immediately post the solution up for folk to see.
Kind Regards
zm

 

[Fluff]

In that case you need to supply links to ALL sites where you have asked this question.