Chris_ach16
New Member
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
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