[AccessD] super critical:PercentRank function in Access

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
Jim Hale

Function RankExample() As Double
'Under tools references check the Microsoft excel library
Dim appExcel As Excel.Application, myrange As Range, intI As Integer
Dim intTestvalue As Integer

On Error GoTo ErrorProc:
intTestvalue = InputBox("Input a number from 100 to 340 divisible by 10")
If (intTestvalue >= 110 And intTestvalue <= 340) Then
    If (intTestvalue / 10 - Int(intTestvalue / 10)) <> 0 Then
        MsgBox "The number must be divisible by 10"
Else
        Set appExcel = Excel.Application
        With appExcel
        .Workbooks.Add
        'fill cells with test data
        For intI = 1 To 24
            .Cells(intI, 1) = 100 + intI * 10
        Next
        Set myrange = .Worksheets("Sheet1").Range("A1:A24")
        RankExample = .WorksheetFunction.Rank(intTestvalue, myrange)
 	  MsgBox intTestvalue & " is " & RankExample & " in rank" 'show the
answer"
        'places the formula in a cell on the worksheet
        .Range("d5").Formula = "=rank(" & intTestvalue & ",A1:A24)"       
        .Visible = True
        .ScreenUpdating = True
        .Quit
        End With
        Set appExcel = Nothing
    End If
Else
    MsgBox "The number you have input is outside the range of values"
End If
The_End:

Exit Function
ErrorProc:
 MsgBox Err.Description
    Resume The_End
End Function

-----Original Message-----
From: Klos, Susan [mailto:Susan.Klos at fldoe.org]
Sent: Sunday, June 08, 2003 6:05 PM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] super critical:PercentRank function in Access


Has anyone used Worksheet functions in Access?  I am trying to rank a column
of numbers in a table and I have no idea even how to begin.  HELP!! Please.
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030609/3c546bc9/attachment-0001.html>