MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Tom and Richard... On the Right Track (Excel Query)


Posted by Brett Holcomb on October 24, 2001 4:41 PM

Tom and Richard,

You guys almost have me where i need it. I am, however trying to do this many times thus automated with VBA.

Here is what I have... A database (in Access) with a huge list of times. I like to print out various reports, some of them with the top 25 from all years, some the top 25 from this year, some the top 10 this year, etc. Instead of going to each Excel page and updating them manually (determining where the top times would fit in, etc), I would like to pull the data from the database and then specify in Excel what kind I am looking for (a top 25 list, a top 10 list, etc). Then, when I enter faster times from the next meet into the database and refresh my queries, it will automatically update with any new top times in the right sheet (top 25 sheet, top 10 sheet, etc). I don't know if this makes sense... I really really appreciate your help.


Posted by Richard S on October 24, 2001 5:18 PM

Tom's a bit more of a pro at this stuff than me I think, but a possible suggestion might be to name the range that has all your data in it, and use Tom's formula to rank them, but instead of the cell references in the second argument, use the name. Hopefully this will mean that when your list is updated, you can just copy Tom's formula from top to bottom each time, and not have to re-enter it. You could then have the whole lot with a different name, sya "current year", and on a separate sheet use vlookups to return the top 10 names and times, or however many you want. The only manual stuff you would have to do is copy Tom's formula, and sort the list so the lookup works. You could probably right a Macro to do this.
Good Luck!!
Richard

Posted by Tom Urtis on October 24, 2001 5:46 PM

Is this what you are looking for?

Brett,

Here's a macro that I think does what you want.
This selects the "top" 10 times (I put "top" in quotes because really I think you want the lowest times which are usually winners in races)...note that the code calls for "Bottom" in this case.

This macro assumes that:

(1) You have data from column A to column E; adjust as needed.

(2) Your worksheet is Sheet5, adjust as needed.

(3) Your times are in column B, adjust as needed.

(4) Your data begins in A2, with row 1 containing headers.

Finally, this will work no matter how many rows your data fills up as you add to it, hence the unoriginal name of the sub because it serves a dynamic range.

Hope this helps, here it is.

Sub FilterDynamic()
' FilterDynamic Macro
' Macro recorded 10/24/2001 by Thomas Urtis

'Turn off screen updating
Application.ScreenUpdating = False
'Identify your dynamic range
Dim rList As Range
Set rList = Sheet5.Range("A2", Sheet5.Range("E65536").End(xlUp))
'Turn off the AutoFilter if it is on
Sheet5.AutoFilterMode = False
'Now turn the AutoFilter on
With rList
.Offset(0, 2).Cells(1, 1).AutoFilter
End With
'Execute the filter for top 10 times
Selection.AutoFilter Field:=2, Criteria1:="10", Operator:=xlBottom10Items
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub


Thank you Richard for the nice compliment in your previous post.

Tom Urtis


Posted by Brett Holcomb on October 24, 2001 6:12 PM

Re: Is this what you are looking for?

Tom,

That Macro looks great except that VBA doesnt like the line: Selection.AutoFilter. Field:=2, Criteria1:="10", Operator:=xlBottom10Items

When it gets ot this point, it says "Application Defined or Object Defined Error" and highlights that line. Any ideas?

Brett

P.S. You and Richard amaze me, I never thought anyone could know this much about a program...shows what I know. Thanks for taking the time.

Posted by Tom Urtis on October 24, 2001 6:24 PM

Re: Is this what you are looking for?

Brett,

I tested this and it worked for me before I sent it to you, so there may be a few reasons why:

(1) If you pasted this into a module straight out of the message board, sometimes line breaks and spaces occur where none existed before.

(2) Maybe your sheet references are incorrect. In my example, Sheet5 may not necessarily mean what you see as Sheet 5 on your worksheet tab. Go into the VBE and see which sheet number Excel thinks your actual worksheet in question is, and adjust the code accordingly.

(3) Maybe your actual range is not consistent with how I wrote the macro example? Maybe your times are in a different column than B or start in a different row and that needs to be adjusted.

If you still have problems, email it to me at tomurtis@home.com and I can look it over later tonight.

Tom