=IF(B2="","",RANK(B2,$B$2:$B$50,0)) range as long as you are likely to need
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Once I copy a lot of these if functions my excel file balloons into a monster over 15mb and becomes unusable. The problem is I have long if functions and also will need to copy down many of them to allow the user to enter lots of data. Is there some kind of option in VBA to update the spreadsheet for this?
Add 2 buttons(1 for eatch macro) and use the function when you really need it.
Sub macro1() Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RANK(RC[-3],R2C2:R10000C2,0))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E10000"), Type:=xlFillDefault Range("E2:E10000").Select End Sub Sub clear() Range("E2:E10000").Select Selection.ClearContents End Sub
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks