User Forms to Search, Display and result

[sandy armstrong]

Hello Everyone.
I have a question and i would appreciate if any one can help....
Attached is a user form with 3 Search critira. I would like it to seach the worksheet display result in text box and on update that filtered list add a number(From the userForm)into in a column that says ppo #. any guidance in this will help.

[sandy armstrong]

can anyone help? did i not explain it right????

[Guido Geurs]

Attached demo will:
- load all the existing values from the sheet into the Combo lists.
- Search according to the lists selection.
- Add in the text box some values from the found records.

Question: for each found record, must ppo# be automatically added in a column or must the user decide for it and add it yes or no?

[sandy armstrong]

Wow thanks Guido for this i really appreciate this once again your the best.... I would like to have the user add a PPO# to the column after each record found is it possible to keep adding PPO#'s to that same column then seperate with a comma everytime a new one is add then time stamp in the next coloumn everytime a new one is add

[Guido Geurs]

Attached is a demo how it can be done.
I have added a Msgbox for the user to add PPO# ? YES - NO

[sandy armstrong]

Omg!!!! wow Ur the BEST!!! How do I change it to autmatically add the ppo number to the records found?? Would this great I think it would be easier that way..
THanks so much

[sandy armstrong]

Guido, I have one more question, i need your option on which way would be the best way to handle this... If i want to date stamp after everytime a ppo# was entered how do i keep keep that date and time even though a new ppo was added with a new date and time what im trying to say is there a better way of keep track of ppo# and there date beside the way that im doing it.

[sandy armstrong]

Guido How do i change vba code to run search even if there is nothing selected in Sub Specialty. Only run on 2 options Specailty and State

[Guido Geurs]

Just add checkboxes and select the one which you want to use. (see attachment)

PS:
- I have added a menu button in stead of a command button.
- are there much lines (records) in the sheets?
Because if so, it's better to use arrays: much, much faster !!!

[sandy armstrong]

Yes I could have over 20,000 records in the sheet at once. Thanks for help me with this I have recived the best advice from you. Sorry for the late response

[sandy armstrong]

How do i get it to automatically put the ppo# i do not want to have the users accidentally add the ppo# twice, thanks for always answering my questions...

[Guido Geurs]

This is a demo how to add the PPO automatically and how to use arrays (tables in the memory) when you have to many records.

[sandy armstrong]

Thanks GUido That works Awesome!!!!!! I Can I transfer that user form to this datebase

[Guido Geurs]

Sandy,
There is a major problem in the workbook: the data in the columns to search on are not standard !
What I mean: there is 2x "Emergency Medicine", 2 x "Pulmonology", "NY" ,... once ending with 1 space, once with 2 spaces, ....
If you want to search on these data, they must be have a standard value like "NY" and not " NY" or "N Y " or "NY "...
The only way is to write a macro for standardization the data in the sheet.

[sandy armstrong]

Hey Guido Im sorry for the late respone. But thanks for looking at this for me. I understand there is no standards with the states or specailties. This data was collected from 3 different sites by mulitiple people. What i was trying to do is every time a college of my does emails blast using word mail merge she can track who she sent to and the date with the number of ppo# I thought creating a user form would be easier the trying to do some hard coding since iam very new to VBA. I really apprecaite your guidance and expertise so much Thank you...

[Guido Geurs]

Would it help when I write a form for standardizing the data in the 3 columns?

[Guido Geurs]

Hello Sandy,
I'm back on this call and working on a tool for standardizing the data.
Do You still want it?

[sandy armstrong]

Yea that would be awesome!!! thanks SO much!!

[sandy armstrong]

I would love that... Thanks Guido that awesome...

[sandy armstrong]

Hey Guido I am going to Uplaod a search user form that im using Can i use that same one that you helped me with but with some modifiations and changes to fit this porpose or do i need to create something from scatch.

[Guido Geurs]

As promised, attached is a tool to standardize the data in the sheet.

PS:
There is a note of Bytes in which they ask to close a call once an answer is accepted as a solution for the call.
If you have an other question (even in the same project), please open an other call.

For this call: I must still look at your last question for the modification of the "search User form".
But if there is a problem with the "Standard" tool, please open an other call.

[Guido Geurs]

For your last question with the file "Bytes.zip": there is no Userform2 in the VBA environment !?

Please if this is a new search form, place it in a new call (see previous answer)

[sandy armstrong]

Wow thanks Guido!!!! this is a great tool that we can use to Standardize new data. this is perfect!!!!!! thanks guido i really thank ful you could help me with this I knew we were going to run into troble if we didnt have everything standardize.

[Guido Geurs]

This is a new version of the tool.
- Easy to use
- The possibility to change more than one wrong data at once. (like: "#NY", "NY##", "Ny", "ny", ... to "NY")
- Save standards to file for later use.
-....

[sandy armstrong]

Hello, GUido thanks for this tool I was trying to use the user form that is used to add the PPO# but i keep getting an error on
ARRDUMP = Range("G2").Resize(ROWlastREC, 2)- this line
But this is sooo cool Im in love with this! thanks soo much

[Guido Geurs]

I think we have to pick up the user-form "Search" again because I have seen that the code is NOT OK.
We have stopped to develop it because we had first to standardize the data and develop the tool for it = user form "standardize".
Now that we have standard data, we can continue with the user form "search".
I will pick it up as a next project.