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]

This would work would, wouldn't it?

Give it a try.

 

[alocke]

Ok, so I tried it and it still doesn't seem to work. Could I add an additional line onto my already existant rank code, telling it to change the blanks to zeros if they are in this particular problem. Either that, or maybe I dim my elements under something other than single... (As they are percentages of large values, they're likely to be many many sig figures, does single allow this?)
Thanks.

 

[Peter_SSs]

You could easily change the column V values to 0 instead of ""
Change
=IF(ISNUMBER(U9),PERCENTRANK((U9,U209,U409),U9),"")
to
=IF(ISNUMBER(U9),PERCENTRANK((U9,U209,U409),U9),0)

.. or if you are doing it with the earlier code, make this change

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

 

[alocke]

That wont work I'm afraid, as there are values lower than 0 in the U column, therefore that would affect the percent rank value.

I think the best way would be:
If Case1 has any of those errors. i.e ( [blank] - value, [blank] - [blank], etc.)
could I write a bit of code which ignored these errors, because I reckon thats the problem.

Cheers.

 

[Peter_SSs]

That wont work I'm afraid,..

Oh, I thought that is what you were asking to do ..

Could I add an additional line onto my already existant rank code, telling it to change the blanks to zeros ...

I think the best way would be:
If Case1 has any of those errors. i.e ( [blank] - value, [blank] - [blank], etc.)
could I write a bit of code which ignored these errors, because I reckon thats the problem.

We could certainly do something in the code to ignore the problem, but what do you want to happen to the whole block of values if the problem occurs?
- Don't move it to either sheet?
- Move it to Sheet5?
- Move it to Sheet6
- Something else?

 

[alocke]

Oh, I thought that is what you were asking to do ..



We could certainly do something in the code to ignore the problem, but what do you want to happen to the whole block of values if the problem occurs?
- Don't move it to either sheet?
- Move it to Sheet5?
- Move it to Sheet6
- Something else?

Peter, I realised something very obvious. As I thought the cells which fell in that particular criteria did so because they we're completely and utterly non existant. I was trying to get my head around it all day because it is impossible.

Error on my behalf. Sorry for asking all those pointless questions

Just to let you know Peter, the particular codes you helped me out with are running smoothly. They're very user friendly also so it allows me to change them to fit a range of varying criteria.

So thank you so much, its very much appreciated!
Alex.

 

[Peter_SSs]

Just to let you know Peter, the particular codes you helped me out with are running smoothly. They're very user friendly also so it allows me to change them to fit a range of varying criteria.

So thank you so much, its very much appreciated!
Alex.

Cheers. Glad we got there in the end.