AutoFilter lessthan greater than

[meredth78]

Hi guys,
I need help autofilter a table based on a high and low range determined by another sheet. below is the macro i recorded...

Selection.AutoFilter Field:=6, Criteria1:="<26", Operator:=xlOr, _
Criteria2:=">23"

say the data table is on sheet2, what i need is to filter the table to show data between the values of sheet1 cell a1 and sheet1 cell a2.

thanks

[meredth78]

looks like i stumped a few

[Ecniv]

If you OR you will bring back everything

How are you setting the autofilter? Code from a menu button?
You'd need a reference to sheet2, then use sheet2s autofilter, and change the xlor to xland.

[meredth78]

The xland...xlor isn't my issue. I need to figure out how to reference a cell in another sheet from the autofilter sub.

What i am doing is taking a data sheet (12000+ rows and 23 columns) for an IRL practice session and filtering out specific drivers then filtering out lap times between a max and min time.

If you haven't figured it out yet I'm NOT a programmer.

[Ecniv]

I don't think you can directly reference to the other sheets cells, but you can run a macro that does reference those cells and then sets the autofilter.

See attached

[meredth78]

That does exaclty what i needed it to. Thank you very much!

p.s. What books would you suggest picking up to help me learn Excel VBA?

[Ecniv]

I'm not sure. Wrox do some really good books and my preference is for Sybex. O'reilly are pretty good.

one thing to look for is the level it is aimed at.
If you go beginner -> intermediate, the book is likely not to be used once you get there. But if you don't mind stretching a bit usually I go for an intermediate to advanced. especially if it is written well and can be used as a reference guide. Sybex book I got for Access were like that.


Alternatively you can record most macros, see how excel does it then change all the selection objects into their relevant objects (selection of cell(s) is a range, activeworksheet is a sheet or worksheet etc).
Best thing is if people can give you different tasks to complete on a spreadsheet. You know, the repetative ones that don't require much thought. Like the formatting of sheets, perhaps auto generation of pivot tables from a table/file of data. Simple processing. Ecah of these allows you to experiment with coding in different ways and get better with a goal to achieve at the end.

If you look around your office, you should find some of these... or perhaps some paper based tracking or something you can try to convert into sheets and practice your coding.

Oh and with the books, try to get your hands on them first to flick through. Search online for the books (amazon?) and write down the titles. Go to the local book store and see if they have them. Spend 5 mins or so leafing through and see if the style of the book suits you. Then you can either buy immediately or order on line (possibly cheaper). Alternatively some older books (still good) will be available at car boot sales, computer shows, or on old stock going out of book stores. might be worth a quick browse there too.

[meredth78]

Thanks Ecniv.

Will the VBA for Office 2007 be the same as '03, with the exception of added features?

[Ecniv]

Not sure. As far as I know yes should still work. Depends if they have moved toward .Net or not. Post up if you find out.

If the code does not debug>compile... Then MS changed things.