| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA RANK
is there a way that I can make this formula into
VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA RANK
Not sure that I understand your question but the following examples of VBA
code might put you on the right track. Range("D1") = WorksheetFunction.Rank(Range("A7"), _ Range("A$7:A$200")) + WorksheetFunction.CountIf(Range("A$7:A7"), _ Range("A7")) - 1 Range("E1") = WorksheetFunction.Min(Range("A$7:A7")) Range("F1") = WorksheetFunction.Max(Range("A$7:A7")) -- Regards, OssieMac "djdwwoug" wrote: is there a way that I can make this formula into VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA RANK
Thanks, how about looping through the range, like range(D & value) =
WorksheetFunction.Rank(Range("A7"), is this possible? so I can get the top ten "OssieMac" wrote: Not sure that I understand your question but the following examples of VBA code might put you on the right track. Range("D1") = WorksheetFunction.Rank(Range("A7"), _ Range("A$7:A$200")) + WorksheetFunction.CountIf(Range("A$7:A7"), _ Range("A7")) - 1 Range("E1") = WorksheetFunction.Min(Range("A$7:A7")) Range("F1") = WorksheetFunction.Max(Range("A$7:A7")) -- Regards, OssieMac "djdwwoug" wrote: is there a way that I can make this formula into VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA RANK
I really do not understand what you are trying to achieve. Can you give me a
sample list of about 20 numbers and a sample of what the output should be with an explanation of what it is you are trying to achieve. -- Regards, OssieMac "djdwwoug" wrote: Thanks, how about looping through the range, like range(D & value) = WorksheetFunction.Rank(Range("A7"), is this possible? so I can get the top ten "OssieMac" wrote: Not sure that I understand your question but the following examples of VBA code might put you on the right track. Range("D1") = WorksheetFunction.Rank(Range("A7"), _ Range("A$7:A$200")) + WorksheetFunction.CountIf(Range("A$7:A7"), _ Range("A7")) - 1 Range("E1") = WorksheetFunction.Min(Range("A$7:A7")) Range("F1") = WorksheetFunction.Max(Range("A$7:A7")) -- Regards, OssieMac "djdwwoug" wrote: is there a way that I can make this formula into VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA RANK
Thanks a lot for your help, I figured it out.
I have another question, how do I deal with Blanks and nulls "OssieMac" wrote: I really do not understand what you are trying to achieve. Can you give me a sample list of about 20 numbers and a sample of what the output should be with an explanation of what it is you are trying to achieve. -- Regards, OssieMac "djdwwoug" wrote: Thanks, how about looping through the range, like range(D & value) = WorksheetFunction.Rank(Range("A7"), is this possible? so I can get the top ten "OssieMac" wrote: Not sure that I understand your question but the following examples of VBA code might put you on the right track. Range("D1") = WorksheetFunction.Rank(Range("A7"), _ Range("A$7:A$200")) + WorksheetFunction.CountIf(Range("A$7:A7"), _ Range("A7")) - 1 Range("E1") = WorksheetFunction.Min(Range("A$7:A7")) Range("F1") = WorksheetFunction.Max(Range("A$7:A7")) -- Regards, OssieMac "djdwwoug" wrote: is there a way that I can make this formula into VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA RANK
Hi again,
Following line tests for a blank cell. You can use a variable representing the range. If Range("A1") = "" Then 'place code here for blank cell Else 'place code here for not blank End If Can also test for not blank. If Range("A1") < "" Then Note that there is no space between the double quotes. -- Regards, OssieMac "djdwwoug" wrote: Thanks a lot for your help, I figured it out. I have another question, how do I deal with Blanks and nulls "OssieMac" wrote: I really do not understand what you are trying to achieve. Can you give me a sample list of about 20 numbers and a sample of what the output should be with an explanation of what it is you are trying to achieve. -- Regards, OssieMac "djdwwoug" wrote: Thanks, how about looping through the range, like range(D & value) = WorksheetFunction.Rank(Range("A7"), is this possible? so I can get the top ten "OssieMac" wrote: Not sure that I understand your question but the following examples of VBA code might put you on the right track. Range("D1") = WorksheetFunction.Rank(Range("A7"), _ Range("A$7:A$200")) + WorksheetFunction.CountIf(Range("A$7:A7"), _ Range("A7")) - 1 Range("E1") = WorksheetFunction.Min(Range("A$7:A7")) Range("F1") = WorksheetFunction.Max(Range("A$7:A7")) -- Regards, OssieMac "djdwwoug" wrote: is there a way that I can make this formula into VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA RANK
Thanks everything is working.
"OssieMac" wrote: Hi again, Following line tests for a blank cell. You can use a variable representing the range. If Range("A1") = "" Then 'place code here for blank cell Else 'place code here for not blank End If Can also test for not blank. If Range("A1") < "" Then Note that there is no space between the double quotes. -- Regards, OssieMac "djdwwoug" wrote: Thanks a lot for your help, I figured it out. I have another question, how do I deal with Blanks and nulls "OssieMac" wrote: I really do not understand what you are trying to achieve. Can you give me a sample list of about 20 numbers and a sample of what the output should be with an explanation of what it is you are trying to achieve. -- Regards, OssieMac "djdwwoug" wrote: Thanks, how about looping through the range, like range(D & value) = WorksheetFunction.Rank(Range("A7"), is this possible? so I can get the top ten "OssieMac" wrote: Not sure that I understand your question but the following examples of VBA code might put you on the right track. Range("D1") = WorksheetFunction.Rank(Range("A7"), _ Range("A$7:A$200")) + WorksheetFunction.CountIf(Range("A$7:A7"), _ Range("A7")) - 1 Range("E1") = WorksheetFunction.Min(Range("A$7:A7")) Range("F1") = WorksheetFunction.Max(Range("A$7:A7")) -- Regards, OssieMac "djdwwoug" wrote: is there a way that I can make this formula into VBA code that looks up a given range see below =RANK(A7,A$7:A$200)+COUNTIF(A$7:A7,A7)-1 and then used VBA the to get the Large and Small values Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
| Rank(A1,C1:C5) - Rank using 2 ranges | Excel Worksheet Functions | |||
| Rank where lowest value is highest rank | Excel Worksheet Functions | |||
| Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. | Excel Worksheet Functions | |||
| Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Linear Mode
