"Save to database" macro: transfering data from one sheet to another based on Identifier

[chris_ach]

Dear VBA Experts

After spending many hours trying to create a "Save to Database" macro, I have sadly given up. My VBA know-how is simply not enough.

Can anyone perhaps help with the following task:

I have a form which I use as tool. Once the form is filled out, I want to transfer all the info into a second sheet that I have defined as the database.

The database is arranged with the variable names on the left (from top to bottom) on the A column. The form information should then be stored also vertically to match the variables. The first (highest) entry is the identifier of the entry itself and bellow is the rest of the form fields.

To the right of that (from Column B and further), there will be entries with their corresponding variable values all the way down to the end of the variable list.

The problems:

1. the cells of the form are not continuous and are all over the place.

2. I have to be able to create the entry if new. Otherwise I have to check the top row (entry identifier) to find the already existing
entry and overwrite the data to that specific column.
3. The data in the form has to be transposed because the database is vertical

The conditions:

1. There are a finite number of variables --> a maximum of 100 Team members
2. There are unlimited number of entries in the data base
3. If a field in the form is not filled-out then it stays blank in the database


How do I do this?

Here is an example:


In Sheet 1 (Teams_Form) I would have some rows with fields to be filled-out:
(the commas represent the next cell to the right)

Team_north
John, Engineer, 1978, male
Tom, Doctor, 1965, male
(empty)
Janine, Teacher, 1999, female
Chris, Electrician, 1985, male

.... etc.



This means in Sheet 2 (database) I would have a first column with all the variable names as follows:

Identifier
Member1
Member2
Member3
Member4
Member5
Occupation1
Occupation2
Occupation3
Occupation4
Occupaton5
Date1
Date2
Date3
Date4
Date5
Gender1
.... etc.


and then next to that on the right there would be entries (one per column) like for example:


Team_north
John
Tom
(empty)
Janine
Chris
Engineer
Doctor
(empty)

Teacher
Electrician
1978
1965
(empty)
1999
1985

.... etc.


I would really appreciate some help!

Greetings,
Chris

[Bernie Deitrick]

I would start by defining named columns on the form sheet that match your variable names:

Member
Occupation
Date

And have a named cell "TeamName"

The advantage of naming the ranges is that you can simply loop over the cells of column A to read/write the values of interest.

Here is an example workbook that transfers data to and from a database sheet.

Database transfer.xlsm

[chris_ach]

Thanks a lot Bernie. That was fast and great!

It works great. I forgot perhaps to mention, that my ranges are not continuous. This means I probably wont have the luxury to have a nice area like for example from B5 to E14. Since I will be recycling a relatively established form that I cannot change, I will have fore xample always 2 empty rows between team members and there is a sub-classification on the form for back-up team members. These two member-type sections are separated by 5 empty rows. Furthermore, there are empty columns between some of the titles on the form. For example between Name and occupation and after gender and the next title.

Any suggestions to overcome this?

Greetings,
Chris

[Bernie Deitrick]

I think this will work - just a little logic to skip around the sheet given the structure you show.

Database transfer_b.xlsm

[chris_ach]

That is indeed a straight-forward but elegant work around. Thank you.

May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

Thank you so much for your help.

[Bernie Deitrick]

A little more than just nesting, since you changed how the values are labeled.

Database transfer_c.xlsm

[chris_ach]

That is indeed a straight-forward but elegant work around. Thank you.

May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

Thank you so much for your help.

Database transfer_c.xlsm

[chris_ach]

That is indeed a straight-forward but elegant work around. Thank you.

May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

Thank you so much for your help.

[chris_ach]

That is indeed a straight-forward but elegant work around. Thank you.

May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

Thank you so much for your help.

[chris_ach]

Sorry about the multiple posting. I kept getting an error and before I notice, it was posted many times. Can I delete posts?

[Bernie Deitrick]

Don't worry about the extra posts - I only replied to one

[chris_ach]

Cool.

Once again 100000000 x thanks!