VBA project to search, organize and return

[brooksc29]

Hello all,

I am working on a project that is a bit over my head.

I have a workbook with daily information created on separate tabs (worksheets). Each worksheet is titled by date.

The format on each daily worksheet has rows under a headline "Facts", rows under a headline "Rumors" and rows under a headline "Highlights"

In each row, starting in column F, will be Date (column F)/ Team (G)/ Name (H) /Notes (L)/ Source (T) (there are some merged cells for formatting purposes).

The VBA will be needed to run a search program on a separate tab called PLAYER LOOKUP. I want the user to choose a name from a data validated drop down box (cell E3), and the VBA will take the name in that box, find and return all entered data by the chosen name and return the data under the appropriate headline of "Facts", "Rumors", or "Highlights" (it should be able to tell where the data is being pulled from).

Can anyone help me get started with this? (at least!!??)

Please let me know what I can provide to make this easier!

Thank you so much!
C

 

[mumps]

The first thing to do is unmerge the merged cells. You should avoid using merged cells because they create havoc for Excel macros.
For clarification:
You want to return only the data under the headers "Facts", "Rumors" and "Highlights". If that is correct, what columns are those headers in and in which columns in PLAYER LOOKUP do you want the data returned?

 

[brooksc29]

Thanks for your reply

I will certainly reformat to eliminate merged cells.

On each sheet it looks as follows
FACTS across Row 1 A:E
Row 2 has Date in Col A: Team in Col B; Name in Col C: Notes in Col D: Source in Col E
Rows 3-9 are set up to enter information under FACTS. Over time, this may include more than rows 3-9.


RUMORS across Row 10 A:E
Row 11 has Date in Col A: Team in Col B; Name in Col C: Notes in Col D: Source in Col E
Rows 12-18 are set up to enter information under RUMORS. Over time, this may include more than rows 12-18.

HIGHLIGHTS across Row 19 A:E
Row 20 has Date in Col A: Team in Col B; Name in Col C: Notes in Col D: Source in Col E
Rows 21-35 are set up to enter information under HIGHLIGHTS. Over time, this may include more than rows 21-35.

IN PLAYER LOOKUP
The drop down box where the user chooses name is in E3

FACTS is F6:Y6 (MERGED CELLS)
Row 7 has Date in Col F: Team in Col G; Name in Col H:K (MERGED CELLS): Notes in Col L:S (MERGED CELLS): Source in Col T:Y (MERGED CELLS)
Rows 8:16 are under FACTS

RUMORS is F17:Y17 (MERGED CELLS)
Row 18 has Date in Col F: Team in Col G; Name in Col H:K (MERGED CELLS): Notes in Col L:S (MERGED CELLS): Source in Col T:Y (MERGED CELLS)
Rows 19:28 are under RUMORS

Highlights is F29:Y29 (MERGED CELLS)
Row 30 has Date in Col F: Team in Col G; Name in Col H:K (MERGED CELLS): Notes in Col L:S (MERGED CELLS): Source in Col T:Y (MERGED CELLS)
Rows 31:50 are under HIGHLIGHTS

I will be reformatting this page but this is how it is set up now. There is more room under the HIGHLIGHTS row to enter data than under FACTS or RUMORS. I'd like to be able for the VBA to add rows automatically under FACTS or RUMORS if there is more data to return than empty rows available, but that could be updated manually if it can't be done in VBA.

So basically, I'd like to be able to have the VBA find the word FACTS, RUMORS, or HIGHLIGHTS, then return the date below finding those words in order, by date, and with the appropriate info in each column on PLAYER LOOKUP.

Thank you very much!

 

[mumps]

I think that it would be easier to visualize how your data is organized and test possible solutions if I could work with your actual file which includes any macros you are currently using and the cells unmerged. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[brooksc29]

Thanks Mumps, I think sharing the file makes a lot of sense.

https://www.dropbox.com/s/rh8rx96mr5tzili/2019 NPB & KBO Daily Updates Blank Template.xlsx?dl=0

I reformatted PLAYER LOOKUP so there are no merged cells so the previous post is now different in terms of specific cells where things live.

Specifically, I need to for the user to be able to search a name in the drop down box on PLAYER LOOKUP, then have the date return under each headline corresponding to that name, returning all the data for each headline arranged by descending date.

please feel free to email me with any questions as you take a look
brooksc29@gmail.com

 

[mumps]

After looking at your file, I noticed that there were still some merged cells, particularly in the "Date" sheets. I unmerged these cells and formatted them as "Center Across Selection" which has the same visual effect as merging without actually merging the cells. I could have a working macro for you quite quickly if we could make a slight change in how your data is organized in the PLAYER LOOKUP sheet. At present, you have the FACTS, RUMORS and HIGHLIGHTS underneath each other. This complicates pasting the data because of the varying number of rows that have to be inserted. It will also complicate sorting each section by descending date. These problems can be easily solved if we could have those 3 sections beside each other rather than underneath each other. Would this work for you?

 

[brooksc29]

Mumps,

I'm not sure what you mean by "Center Across Selection" - but if it has the same visual effect, then I'm happy to learn about it.

Do I only need to reorganize the PLAYER LOOKUP sheet? That could probably be done. However, the individual "date" sheets need to be formatted to print on one page, so reorganizing their layouts may not be ideal. Is that ok?

 

[mumps]

"Center Across Selection" is a useful way of avoiding merged cells...just do a quick Google search.
I can reorganize the PLAYER LOOKUP sheet and send you the file when done. The individual "date" sheets don't have to be changed.

 

[brooksc29]

Got it.

Reformatted PLAYER LOOKUP. Please take a look and see if it will work.

Thank you!


https://www.dropbox.com/s/edpnq76imems78g/2019 NPB & KBO Daily Updates Blank Template2.xlsx?dl=0

 

[mumps]

Click here for your file. As you choose a different player in E3, the player will be added to the list.