how to return a series of values in a given range

[globulous]

I have a sheet that acts as a schedule for the NHL with the away teams in Column C and the Home teams in Column G.. There are 1230 games in the schedule, so my range runs from C or G7:1236 (C or G depending on whether i want Away or Home)

What i want to be able to do is develop a macro that allows me to click a button (which i will setup later) so i can fetch all of the games for a given team and have them pasted on another sheet..

Say i want to pick out all of EDM's 82 games from the possible 1230..
I want something that scans BOTH Column C (for away games) and Column G (for home games) and returns ALL THE ROWS where "EDM" occurs in Column C OR Column G..

I then need those rows pasted on another page

Can someone help me code this??
I suppose i could do it manually by recording a macro, but this would be immensely long when you consider i'd have to filter the results twice (to get home and away) and then sort them all on the 'paste' page...

[mewingkitty]

Aye, could do it without much trouble at all.
Might help to see a sample of your worksheet, and to know where the "Other page" is going to be...

Sounds like you want a simple search engine which you can enter "Edmonton" and have it list all of edmontons games, then type in "Vancouver" and have it list all of vancouvers games, etc.

correct?

[globulous]

that's exactly it...
i was thinking of setting up 30 teams in a menu and clicking the appropriate one, but i suppose a 'search' engine would save alot of hassle.. you could probably do it with 1 macro, instead of 30..


i'm not sure how to setup a sample.. my excel sheet is about 30megs..

lets say for example the 'paste' sheet is called "TEAMS"...
It will be in the same workbook.
again, the 2 columns i want to search from are C and G (in the sheet "SCHEDULE"), with a range running from 7:1236...

"TEAMS" will have the identical layout as the originating page, and the entries should be pasted starting at Row 7, same as the originating page..

[mewingkitty]

I'd recommend something like this.
Macros must be enabled.
Type in the name and press "Enter" on your keyboard

[globulous]

that's exactly what i want...

now considering you whipped that up so quickly, is there a way you could show me how to code that so i could apply it my own book??

i tried to find the code but i don't know how to access it...

i'm not really familiar with code, but have had some experience in school with programming.. i can usually look at a given code and mess around with it to figure it out on my own and get what i need out of it...

[globulous]

i'll see if i can post a sample of my sheet... gimme a minute, i have to trim alot of fat first

[JBeaucaire]

Press Alt-F11 to open the VBEditor.

Press Ctrl-R to open the Project - VBAProject on the left if it isn't already open. The macros will be found in the modules in this window.

There are regular modules (Module1, Module2, etc) where regular macros are stored, Sheet modules (Sheet1, Sheet2, etc) where macros designed to operate automatically within a specific sheet are stored, and ThisWorkbook module where modules intended to operate automatically against the entire workbook are stored.

[globulous]

here's a sample ....

Note the columns actually continue ALL the way to IV..
but i need the entire rows posted so that shouldn't matter.

the range runs from 7:1236

and need the data obtained from "schedule", pasted to "teams" starting at A7


I have a Custom Menu in this workbook with about 6 macros running from as buttons..
Ideally i'd like to setup this search as a Macro so i can have it as part of the Custom Menu. Instead of having a 'button' to click on, it would be nice if i could type in the teams right on the menu.. build the 'search box' into the menu where the button would be....

capiche??

[mewingkitty]

Click on the developer tab
click on "Design mode"
doubleclick on the textbox.

p.s.
Change it to something like:

Please Login or Register  to view this content.

[globulous]

hmmmm..

i don't know enough about VB to edit that to suit my sheet...

[globulous]

Although mewingkitty's "name search thing.xls" in post #4 is basically what i'm looking for, I don't know how to alter his code to suit my own sample...

My sample workbook is included here...


What i want to do on the "teams" sheet is to have a menu (Custom macro menu) where i can retrieve the rows where a certain Team is playing, but ALSO to retrieve just the road games, or just the home games for that team..

basically a search box for each team, but with the option of selecting ALL games, HOME games or AWAY games...


any game for that challenge??