object defined error

What is wrong with the statement below

Selection.Offset(0, 1).Value = "=Rank(Selection.Value, ""(Selection.cell)"" & "":"" & ""(Selection.Offset(x, 0))"")"

x is the range of cells that have values in them.

thanks

i am using this in VBA I just dont know how to use the rank function without actual cell names. Instead of RANK(A1,A1:A10,0) I want Rank(selection.value,(selected cell)selected cell+5),0)

or having 5 as a symbol that signifies how many cells have values in them

something like this:

a = 0
        Do Until Selection.Offset(a, 0) = ""
        Selection.Offset(a, 1).Formula = "=RANK(" & Selection.Offset(a, 0).Value & "," & Selection.Resize(x).Address & ", 0)"
        a = a + 1
        Loop

that works great for a single element, what would i change if i had it in a loop so im still looking at the same range but the second element?

x = 0
        Do Until Selection.Value = ""
        Selection.Offset(1, 0).Select
        x = x + 1
        Loop
        a = 0
        Selection.Offset(-x, 0).Select
        Do Until Selection.Value = ""
        Selection.Offset(0, 1).Formula = "=RANK(" & Selection.Value & "," & Selection.Resize(5).Address & ", 0)"
        a = a + 1
        Selection.Offset(1,0).Select
        Loop

the range is the value of x as it could change and the formula is the one you gave me, it just doesn't work in a do loop for obvious reasons.

Thanks for your help

ok, the range starts in cell B11 and depending on how many projects are entered depends on how far down it goes. I am not sure what the formula would look like on a worksheet because I am not sure how i would enter it if I don't know the actual range. It would be something between

RANK(B11,$B$11:$B$12,0) and RANK(B11,$B$11:$B$36,0)

and then I would drag it down the column.

I can do it manually in the worksheet doing just what i said above but I am making this for someone so all he has to do is hit a button.

Thanks again

thats great thanks, one last thing, what if I am unsure where it starts can i find the firstrow like you did the last row somehow??