<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: [AccessD] super critical:PercentRank function in Access</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>Maybe this example can get you started. It opens a new workbook, fills column A cells 1-24 with test data and returns the rank of an input number. It also places the formula in the spreadsheet and shows you the sheet. HTH</FONT></P>
<P><FONT SIZE=2>Jim Hale</FONT>
</P>
<P><FONT SIZE=2>Function RankExample() As Double</FONT>
<BR><FONT SIZE=2>'Under tools references check the Microsoft excel library</FONT>
<BR><FONT SIZE=2>Dim appExcel As Excel.Application, myrange As Range, intI As Integer</FONT>
<BR><FONT SIZE=2>Dim intTestvalue As Integer</FONT>
</P>
<P><FONT SIZE=2>On Error GoTo ErrorProc:</FONT>
<BR><FONT SIZE=2>intTestvalue = InputBox("Input a number from 100 to 340 divisible by 10")</FONT>
<BR><FONT SIZE=2>If (intTestvalue >= 110 And intTestvalue <= 340) Then</FONT>
<BR><FONT SIZE=2> If (intTestvalue / 10 - Int(intTestvalue / 10)) <> 0 Then</FONT>
<BR><FONT SIZE=2> MsgBox "The number must be divisible by 10"</FONT>
<BR><FONT SIZE=2>Else</FONT>
<BR><FONT SIZE=2> Set appExcel = Excel.Application</FONT>
<BR><FONT SIZE=2> With appExcel</FONT>
<BR><FONT SIZE=2> .Workbooks.Add</FONT>
<BR><FONT SIZE=2> 'fill cells with test data</FONT>
<BR><FONT SIZE=2> For intI = 1 To 24</FONT>
<BR><FONT SIZE=2> .Cells(intI, 1) = 100 + intI * 10</FONT>
<BR><FONT SIZE=2> Next</FONT>
<BR><FONT SIZE=2> Set myrange = .Worksheets("Sheet1").Range("A1:A24")</FONT>
<BR><FONT SIZE=2> RankExample = .WorksheetFunction.Rank(intTestvalue, myrange)</FONT>
<BR><FONT SIZE=2> MsgBox intTestvalue & " is " & RankExample & " in rank" 'show the answer"</FONT>
<BR><FONT SIZE=2> 'places the formula in a cell on the worksheet</FONT>
<BR><FONT SIZE=2> .Range("d5").Formula = "=rank(" & intTestvalue & ",A1:A24)" </FONT>
<BR><FONT SIZE=2> .Visible = True</FONT>
<BR><FONT SIZE=2> .ScreenUpdating = True</FONT>
<BR><FONT SIZE=2> .Quit</FONT>
<BR><FONT SIZE=2> End With</FONT>
<BR><FONT SIZE=2> Set appExcel = Nothing</FONT>
<BR><FONT SIZE=2> End If</FONT>
<BR><FONT SIZE=2>Else</FONT>
<BR><FONT SIZE=2> MsgBox "The number you have input is outside the range of values"</FONT>
<BR><FONT SIZE=2>End If</FONT>
<BR><FONT SIZE=2>The_End:</FONT>
</P>
<P><FONT SIZE=2>Exit Function</FONT>
<BR><FONT SIZE=2>ErrorProc:</FONT>
<BR><FONT SIZE=2> MsgBox Err.Description</FONT>
<BR><FONT SIZE=2> Resume The_End</FONT>
<BR><FONT SIZE=2>End Function</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Klos, Susan [<A HREF="mailto:Susan.Klos@fldoe.org">mailto:Susan.Klos@fldoe.org</A>]</FONT>
<BR><FONT SIZE=2>Sent: Sunday, June 08, 2003 6:05 PM</FONT>
<BR><FONT SIZE=2>To: 'accessd@databaseadvisors.com'</FONT>
<BR><FONT SIZE=2>Subject: [AccessD] super critical:PercentRank function in Access</FONT>
</P>
<BR>
<P><FONT SIZE=2>Has anyone used Worksheet functions in Access? I am trying to rank a column</FONT>
<BR><FONT SIZE=2>of numbers in a table and I have no idea even how to begin. HELP!! Please.</FONT>
<BR><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
</BODY>
</HTML>