I need a macro to enter data based on data validation lists.

[DorothyFan1]

Hi. I need a macro that allows me to enter data for different departments that I generate using data validation lists. I set up a column for months, another for day and finally one for product. Each column has a data validation list the user can then choose to select entries. Once the entries are made. the user inputs the data that's based on the month and the day it's entered for. Once the data is then input...the macro should then clear the input row of that data allowing the user to input new data based on the validation selections. Can this be done? I've attached a sample spreadsheet showing what I mean. Any suggestions would be greatly appreciated.

[jeffreybrown]

Hi Dorothy,

I will try to help, but not 100% sure what you want to do with the results other than clear them.

If the user selects:
Row 3
Column A >> May
Column B >> Day 5
Collumn C >> Oranges

What Next?

[DorothyFan1]

Actually the data validation lists are for the user only. What I need is one row with the the user selecting the data validation choices and then entering the data for that month and then the macro copies this to the appropriate month column. So a user selects the day, the month and the dept and then manually enters the data on the row and the macro copies this to the month column specified. Once the data is entered...the macro erases the original input to allow the user to select another department, etc and entering the data for that month...and it doesn't have to be only for the current month. The user could select February and the data gets input on that row for that day for that dept in that row.

[jeffreybrown]

Hi Dorothy,

I think I am still missing some aspects of what you desire here, but let's start with the attached example. User enter data in A3:C3 then hit button and data will transfer to appropriate month. You really didn't say what is supposed to happen when they pick Apr, run the macro, pick Apr again. Is the data suppose to stack one on top of the other?

Please let me know how I can edit this for you to make it right as per your requirements.

[DorothyFan1]

Where is the data supposed to be entered? All I see are the data validation ranges. Oh...now I see. There has to be a fourth column that allows the user to enter the data so that after the user selects the month, the day, and the department...the macro copies the data from the fourth column ONLY. So the user selects A, B, and C...but manually enteres the data in D. It's D that counts. The macro needs to take the data that is in D and based on the answers given for the first three columns...appropriately puts this in the correct month and row.

For example, if I choose day 1, Jan, and oranges and in the fourth column I enter a 1...the macro copies the 1 and enters that into the correct month based on what I chose. I copied your sheet and entered what I need to see happen. I show a simple example.

[jeffreybrown]

Hi Dorothy,

Thank you for the additional direction. The only way I can see to get it done is by using some helper cells. In D3 is the first concatenate and then in column H. This does the job but you can't hide the columns, but through the code I unhide the columns, run the macro and then hide them again. If you don't mind the columns (D and H) being visible then just unhide all columns and remove those lines in the code.

[DorothyFan1]

Hi Dorothy,

Thank you for the additional direction. The only way I can see to get it done is by using some helper cells. In D3 is the first concatenate and then in column H. This does the job but you can't hide the columns, but through the code I unhide the columns, run the macro and then hide them again. If you don't mind the columns (D and H) being visible then just unhide all columns and remove those lines in the code.

Fantastic job! It works beautifully!! Now I just need to ask you whether I can change the validation lists accordingly so I can use different departments.

[jeffreybrown]

I would say this is probably possible, but can you tell me how you want to change departments and when, why, etc.?

[DorothyFan1]

I would say this is probably possible, but can you tell me how you want to change departments and when, why, etc.?

The problem is the example I gave...apples, oranges, bananas are just that...examples. If I needed to change the names of the validation list so I can modify the names of the departments I want to use for the sheet I'd like to have that ability. Otherwise I'd be stuck only using this example of the apples, oranges and bananas. I should be able to simply change the names of the list so I can get the macro to simply look at the names of the list and apply the exact same logic that was used in the macro you generated. So it would use the same logic...but applying it to a whole different set of names, but these could be anything. In other words the validation lists aren't static. They can be dynamic in nature...changeable for the need of the departments I have to quantity the reports with. Once that's set...this can be used for any validation list I generate. The only thing this means is I need a way of knowing what part of the code I need to change (which by the way I see you've put in a private hidden macro library.). I need to be able to get to this if I have to modify the list for the department I'm creating this report with. I myself don't know the names of the departments yet. That's why I used the apples, bananas and oranges example. If the macro can be modified in such a way that it could use the same logic regardless of the validation list used for the report...then it would be perfect. In that case I wouldn't have to modify the macro to see how it works. It would just work. So in that case I could generate a validation list of say...birds, dogs, and cats. The macro should be able to "read" the new validation list regardless of names for that list...and still apply it to the way it works as you've generated it.

[jeffreybrown]

Could this be a possiblity?

In C2, pick a department and C3 will filter to that department.

[DorothyFan1]

Could this be a possiblity?

In C2, pick a department and C3 will filter to that department.

The macro isn't working correctly when I change the data validation lists. I get an error "run time error 91: object variable or With Block variable not set". I need the ability to change the lists in cells v2 to x4 for this to work. Actually I think it would be best if the macro would dynamically pick up the validation list regardless of it's length. So I could have three lists up to and including 7 depts. But that's just an example. So once I enter the month, the date, then the only thing I need to do after selecting the dept in c2, I need to be able to change the validation lists used for c3 to let me choose which dept I want to input the value. Once this is done then I think this would work. So the key for me is being able to change the validation list so that cell c3 picks this up correctly even after I delete and reenter the list manually in cells v2 to x4.

[jeffreybrown]

Maybe this can help with the validation lists

http://blog.contextures.com/archives...idation-lists/

I cannot dupliate the error you recieve.