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

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

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

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

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

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

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