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