Search Macro Help!

[edneal2]

I have a spreadsheet that I use to record Entertainment information for a venue, and I am trying to create a search function, such that when i search a certain range of cells in my input sheet, another search page finds the entire range of information pertaining to that one band, and copies all information for every instance in which that band names appears in the input sheet. I have attached the spreadsheet, and attempted to put together the macro myself, along with the help of a friend. However, I am very new to writing macros, and am still very much learning how VB works. Could someone please help me by taking a look at the spreadsheet as well as the "SearchBands" macro that I have started (which may simply have to get scrapped all together). The macro in question is on the "Search Results" sheet, and is drawing its information from "Entertainment Input"

I would like for the search button i have created to start the search macro, search for whichever band i enter into cell "F2" and display all of the information on that band that i have listed below the search cell. The band name that it finds, for example "Obsolete August" should be input under the "entertainment" row (starting in cell F10), but i would like for it to copy all information from column B all the way through column W. Can someone please help!?

[mike7952]

Give this a try

Please Login or Register  to view this content.

[edneal2]

That appears to be working well, I appreciate the help. Are the ranges the only thing I was doing incorrectly?

---------- Post added at 08:42 PM ---------- Previous post was at 08:38 PM ----------

Also, is there something i can add in to copy the formatting along with the entertainer? When I click search, all of the formatting stays the same on the page except the band name that it's finding in column G

[mike7952]

There were 2 things wrong. Yes the range to search. I changed it to look for the band name in Column G. Also the dimension of the array was wrong needed to be (1 to 21, 1 to 1) 21 is the number of columns we want. C to W is 21 columns.

[edneal2]

That makes sense, thanks for taking the time to explain. I am still trying to figure everything out that I've been learning over the last couple of weeks. Thanks again! anything on the formatting issue? just would like it to stay bolded when it finds new information.

[rylo]

Hi

Try using an advanced filter.

In Search Results!G1 enter Entertainment (ie the heading from G8)

then try

Please Login or Register  to view this content.

rylo

[mike7952]

See if you format the columns on the Search Results sheet to the way you want them. Then run the search macro again. Formating should stay.

[edneal2]

Every other column besides G is keeping its formatting, but when i search a different or the same band name to copy over current information, column G is still reverting back to normal formatting. Also something i just noticed is if i search for a band that appears say 5 times, then right after, i search for a band that only appears once, it only deletes over the what it is replacing. Can you help with a way to clear everything starting with row 10 and below, so that previous searches dont remain on the screen in the event of what i mentioned above?

[mike7952]

Try this before adding the data to the sheet. I'm replin from my phone, so untested.

Please Login or Register  to view this content.

---------- Post added at 10:30 PM ---------- Previous post was at 10:21 PM ----------

Actually you will need to add 2 in case there is no data.

Please Login or Register  to view this content.

[edneal2]

That works perfectly. Thanks again I really appreciate all the help. Ill mark the thread as solved!