making a formula over varying ranges

[Hein]

Hallo,
I have a rather tedious job ahaead of me that I would like to automate
as much as possible. I have to calculate the rank of a cell in a
varying number of cells.

Let me explain.
I have a lot of sports results (i.e. speed skating times) without the
position people ultimately ended on. Sometimes there are 20 results,
sometime more, sometimes less. Results are always in a column: e.g.
from b5:b34, or d1:d14 etc

When I want to calculate the rank of each cell I always start with the
cell next to the topcell in the range where the results are (to the
right).

I am not very familiar with making VBA code. I've tried to "record" it
but this didn't work, thereforre my question:
An example of a rank formula I use in cell c3 is:
=Rank(B3;$B$3:$B$6;1)
Is there a way to make this formula automatically, eg by using the
combination <ctrl-shift-Q> AND copying this to the range C4:C6?

To make matters worse: sometime the chain of results is broken (empty
cells in the range). Is it possible to stop the code after the
"end-down" bit and give me the possiblity to enlarge the range to rank
and after this to give "return" and the code continious?

Hope somebody can help me.

Thanks for your trouble
Hein

 

[Guest]

You do not need VBA for what you explained
the Rank function copied down the column will get the job done

if you want to get rid of the error statement in a blank row try this formula:

=IF(ISERROR(RANK(D7,$D$612,1)),"",RANK(D7,$D$612,1))

If the problem is bigger than this please explain what else is needed
how long are you columns? how many columns? how many sheets?...

 

[Tom Ogilvy]

if you select any cell in a column that contains the times and you want
formulas in the column to the right (this assumes that the only numbers in
the column are times and that the numbers are not produced by formulas).

Sub Addformulas()
Dim rng As Range
Dim cell As Range
Dim rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng = Columns(ActiveCell.Column).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
Set rng2 = rng.Areas(rng.Areas.Count)
Set rng2 = rng2(rng2.Count)
Set rng2 = Range(rng(1), rng2)
If rng Is Nothing Then
MsgBox "No times found in this column"
Else
Set rng1 = Intersect(Columns(ActiveCell.Column + 1), rng.EntireRow)
Set cell = rng(1)
rng1.Formula = "=Rank(" & cell.Address(0, 0) & "," _
& rng2.Address(1, 1) & ",1)"
End If
End Sub

 

[Hein]

Thanks Vacation's over and Tom for your answers!

Maybe we are looking for a too complicated answer?

I've been thinking since my last post (quite a headache now ;-) ) an
maybe the answer isn't so complicated.

If you have a line of filled cells (e.g. in a column) you can go to th
last cell by either using <<end-arrow down>> of by using << ctrl-arro
down>>.
Do any of you guys know to capture this in VBA?

2nd question. Sometimes the line of cells is interrupted by a blan
cell. To be sure to not miss a cell: is it possible to stop the macr
after the above mentioned <<ctrl-arrow down>> part so that I have th
change to include more cells after the blank cell, and using the ente
key continues the code??

If I can pull this off I'm sure I can get it working.

By the way vacation's over: the reason for my asking this question i
that I have to do this trick several hunderds of times, maybe over
thousand. Just typing the formula takes a lot of time!

Thanks for any help
Hei

 

[Tom Ogilvy]

I believe my code already answers your questions.