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

 

[alocke]

Also, one more little thing.

Const sFirstCells1 As String = "U8,U208,U408,U608,U808,U1008,U1208,U1408,U1608,U1808"

etc etc
This particular part I had to type 362 different cell values, is there a quicker way to do this. i.e define it as (U(8+200n)) in which n starts as 0 and ends as 362?
If not its fine, it works fine if manually input.
Thanks,
Alex

 

[Peter_SSs]

However when I add the additional code you gave me:

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

Here you added an extra opening ( but not a corresponding closing ). so try

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

 

[alocke]

Here you added an extra opening ( but not a corresponding closing ). so try

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

still get the same thing I'm afraid.

 

[Peter_SSs]

still get the same thing I'm afraid.

Worked without error for me so perhaps you better post the whole code, though if you have a non-working sample code without 362 cells listed it would be abit easier to deal with.

Also, one more little thing.

Const sFirstCells1 As String = "U8,U208,U408,U608,U808,U1008,U1208,U1408,U1608,U1808"

etc etc
This particular part I had to type 362 different cell values, is there a quicker way to do this. i.e define it as (U(8+200n)) in which n starts as 0 and ends as 362?
If not its fine, it works fine if manually input.
Thanks,
Alex

We should be able to deal with this without manually entering all those values. I may not have this exactly right but along these lines. Replace that Const sFirstCells line with this section of code.

Dim sFirstCells As String

Const Col As String = "U"
Const Firstrw As Long = 8
Const Gap As Long = 200
Const Sections As Long = 362

For i = 1 To Sections
    sFirstCells = sFirstCells & "," & Col & Firstrw + (i - 1) * Gap
Next i
sFirstCells = Mid(sFirstCells, 2)

 

[alocke]

We should be able to deal with this without manually entering all those values. I may not have this exactly right but along these lines. Replace that Const sFirstCells line with this section of code.

Dim sFirstCells As String
 
Const Col As String = "U"
Const Firstrw As Long = 8
Const Gap As Long = 200
Const Sections As Long = 362
 
For i = 1 To Sections
    sFirstCells = sFirstCells & "," & Col & Firstrw + (i - 1) * Gap
Next i
sFirstCells = Mid(sFirstCells, 2)

This works when I test it on test examples but something goes wrong when I apply it to my spreadsheet.
The previous code you gave me:

frmlabase = "=IF(#="""","""",PercentRANK((" & sFirstCells & "),#))"
</PRE>

The only reasons I can think of are:
Does this particular piece of code rule out #DIV/0! and #VALUE! errors too?
Either that or the program I'm running the formula and data from is screwing up.
Cheers and sorry for dragging this on,
Alex

 

[Peter_SSs]

The only reasons I can think of are:
Does this particular piece of code rule out #DIV/0! and #VALUE! errors too?

Assuming you mean does it rule out rows where there is an error value in column "U" (or whatever column you have in the code for Const Col As String = "U"), then the answer is "No", it just avoids rows where column U is blank (or contains the null string "").

If there are errors in columns U as well then either ..

a) change the formulas (assuming there are formulas) in column U to return "" instead of the error value *, or

b) try changing this line

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

* If you want help with a) above then
- post one of the formulas from column U
- say which cell that formula comes from
- advise which version of Excel you are using

 

[alocke]

Assuming you mean does it rule out rows where there is an error value in column "U" (or whatever column you have in the code for Const Col As String = "U"), then the answer is "No", it just avoids rows where column U is blank (or contains the null string "").

If there are errors in columns U as well then either ..

a) change the formulas (assuming there are formulas) in column U to return "" instead of the error value *, or

b) try changing this line

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

* If you want help with a) above then
- post one of the formulas from column U
- say which cell that formula comes from
- advise which version of Excel you are using

Based on what appears in each cell (the formula that is) for the ranking seems to be perfect. I'm gonna need to play around with things though as still it doesn't seem to work. Its definitely not your macro though.

The problem arises because in each array I've done lots of working out which produces a set of values. The problem is in certain dates these values don't exist, so I'll toy around with it.

Is there a chance though, as I'm taking about 400 sets of large data from an external source and running many codes at once, that this could be the reason for anomolies? Would it be an idea to let it run for a few hours?
Cheers.

 

[Peter_SSs]

Is there a chance though, as I'm taking about 400 sets of large data from an external source and running many codes at once, that this could be the reason for anomolies? Would it be an idea to let it run for a few hours?
Cheers.

"Running many codes at once" What do you mean by that?

If you have a lot of data and formulas, it may be worth ensuring that Excel's 'calculation mode' is set to manual while the code runs. Something like this

Dim CalcMode As Long

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

'Rest of code goes here

Application.Calculation = CalcMode

 

[alocke]

That helps, I think I may have found the problem. How would I change the significance to 5/6 decimal places?

 

[alocke]

For the percent rank that is.