Simple Rank Macro

[alocke]

Hi,
Just a simple thing - If I want to Rank data in cells A1,A3,A5,A7 amongst one another and also cells A2,A4,A6,A8 too.

The code I'm doing is more complex than this, but if someone can tell me how to do this then I'll be sorted. I think.

Cheers,
Alex

 

[Peter_SSs]

Just two little things, my version has to be more complex in that:
- Instead of there being two ranges, there is going to be 42 each 200 cells apart and consisting of 300 total cells in each range. I'll just have to Dim more elements, i.e just build on what you've written.

I would try to build the code into a loop. Can you be more specific about where the data is that has to be ranked together?

 

[alocke]

Hi Peter,
I'll send you a simplified version, with all the unnecessary data removed.
Just to explain:
There are three bits of data, U8:U43, U208:U243 and U408:U443 in the example sheet I've posted but in total there is about 400 bits of data.
What I need to do is basically rank U8, U208 and U408 with eachother. Then do the same for U9, U209, U409 etc. etc. Until I reach U43, U243, u443.
So exactly what you did with your code except values are spread further apart and there are more ranges.

- As for the -ve values. They do work, just was getting a formula from an external source which was being a bit dodgy. So it does work not to worry.

 

[alocke]

Ok, it won't let me post attachments. But does that make sense?
Thanks,
Alex

 

[Peter_SSs]

If you list the first cell in each range and the number of cells in each range in the 'Const' lines below, see if this does what you want. It is basically going back and using Domenic's original formula idea.


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RankIt()<br>    <SPAN style="color:#00007F">Dim</SPAN> aFirstCells<br>    <SPAN style="color:#00007F">Dim</SPAN> frmlabase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, frmla <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> sFirstCells <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "U8,U208,U408"<br>    <SPAN style="color:#00007F">Const</SPAN> BlockSize <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 36<br>    <br>    frmlabase = "=RANK(#,(" & sFirstCells & "))"<br>    aFirstCells = Split(sFirstCells, ",")<br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aFirstCells)<br>        frmla = Replace(frmlabase, "#", aFirstCells(i), 1, 1, vbTextCompare)<br>        Range(aFirstCells(i)).Offset(, 1).Resize(BlockSize).Formula = frmla<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[alocke]

Peter and others who helped,
Thanks so much this worked perfectly I really appreciate the help. Final little thing - some values are listed as #N/A and this is due to some of my formulas not being able to work as the data I've obtained doesn't exist.
Basically, Just need to tell the code to ignore these values how would I go about doing this?
Cheers,
Alex.

 

[Peter_SSs]

Try replacing the frmlabase line with this

frmlabase = "=IF(#="""","""",RANK(#,(" & sFirstCells & ")))"

and make this small change to the frmla line

frmla = Replace(frmlabase, "#", aFirstCells(i), 1, -1, vbTextCompare)

 

[alocke]

Brilliant,
thank you so much for all the help.

 

[alocke]

I know how to rearrange your code to make it work for a percentrank. Was just wondering if the code will work if I include this edit you showed me. The one which ignores #N/A values?
Thanks.

 

[Peter_SSs]

Give it a try and see? I'm not strong on percentrank.

 

[alocke]

Sorry I mean it didn't work for percentrank.
The change around of code looks like this:

frmlabase = "=PercentRANK((" & sFirstCells & "),#)"

However when I add the additional code you gave me:

frmlabase = "=IF(#="""","""",PercentRANK((" & sFirstCells & "),#)"

it doesn't work, I get a app/obj defined error and this part of code doesn't work:

Range(aFirstCells(i)).Offset(, 1).Resize(BlockSize).Formula = frmla

Thanks,
Alex