Rank

[Stromma]

Hi

I use this to find out the rank in a small range:

Range("B4") = "=RANK(L4,$L$4:$L$19)"

How can i do this with vba in a larger range, Range("L4:L2498")?

Stromma

[Scottie P]

Hi Stromma.

I am not sure about this at all and feel terrible about not trying it out first...but could it be something like:


Application.WorksheetFunction.Rank([B4], [L4,L4:L2498], 1) ?

I am not able to try this out at the moment (in the middle of something else that is eating up most of my PC's resources at the moment) so I am sorry, but thought maybe this would be a step in the right direction for you. :dunno

[Stromma]

Hi Scott and thanks for your reply!

It works fine. Is it possible to make it work for all lines, something like:

For i = 4 To 2498?

Stromma

[Richie(UK)]

Hi Stromma,

Can you explain why you want to loop through nearly 2,500 cells performing a Rank test on each?

Would it not be simpler to add the appropriate formulas to the worksheet? (You can do this in VBA and then delete the contents again after you have examined the data, if that is your wish)

[Stromma]

Hi Richie & Thanks for your reply!

I recently started a small company and this is just to keep track of all costs. I sort the list so the rank isn't really necessary, i just played to see if i could get it to work.

The problem with using a formula is that the rank recalculates immediately, and i don't wan't to do this before i sort the list when all data is entered. I finally used the same approach that i think you suggested. I enter the formula with vba, get the rank value, delete the formula and end up with just the rank value in the cell.

Stromma