object defined error

[lundy]

What is wrong with the statement below

Please Login or Register  to view this content.

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

thanks

[Norie]

This is what you are putting in the cell.

Please Login or Register  to view this content.

That's not a valid formula, that's why you get the error.

What formula are you actually trying to put in the cell?

[lundy]

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

[Norie]

Try this.

Please Login or Register  to view this content.

[lundy]

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?

[lundy]

something like this:

Please Login or Register  to view this content.

[Norie]

I don't think that will work.

What range do you want to put the formula in and what's the actual formula?

[lundy]

Please Login or Register  to view this content.

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

[Norie]

Can you tell us what range you want to put the formula in?

Also, what is the formula as it would look like on a worksheet?

One thing you really need to get rid of is all that Select and Selection.

It's not needed and can actually make things harder.

[lundy]

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

[Norie]

Is the 2nd argument actually the same on each row, but can change depending on the no of projects?

Try this.

Please Login or Register  to view this content.

[lundy]

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??

[Norie]

It really depends on how the first row is determined.