VBA question convert form(s) into a database

[cyrilbrd]

Good day,
Given an excel form filled up by users, said form contains several fields and data.
I would like to convert these into a database by copying the data to the next empty row.
Example of the form:

Book1.xlsm
	A	B	C	D	E	F	G
1	Form filled up and printed by user
2
3	desig1	1
4	desig2	2
5	desig3	3
6	desig4	4
7
8	tbl1		field1	field2	field3	field4
9	Type1		5	6	7	8
10	Type2		9	10	11	12
11	Type3		13	14	15	16
12	Type4		17	18	19	20
13	Type5		21	22	23	24
14
15	tbl2	field5	field6	field7	field8	field9	field10
16	Type6	25	26	27	28	29	30
17	Type7	31	32	33	34	35	36
18	Type8	37	38	39	40	41	42
19
20	tbl3	field11
21	Type9	43
22	Type10	44
23	Type11	45
form

And this is how the database would look like, with each subsequent iteration of the form copied to the next empty row, thus allowing extraction of data from the created DB via formulae or pivot...

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
28	desig1	desig2	desig3	desig4	Type	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10	Field11
29	1	2	3	4	Type1	5	6	7	8
30	1	2	3	4	Type2	9	10	11	12
31	1	2	3	4	Type3	13	14	15	16
32	1	2	3	4	Type4	17	18	19	20
33	1	2	3	4	Type5	21	22	23	24
34	1	2	3	4	Type6					25	26	27	28	29	30
35	1	2	3	4	Type7					31	32	33	34	35	36
36	1	2	3	4	Type8					37	38	39	40	41	42
37	1	2	3	4	Type9											43
38	1	2	3	4	Type10											44
39	1	2	3	4	Type11											45
form

Any suggestion would be welcome.
The idea would be for the file to have only two (2) tabs, on for the form, and one for the DB, the user would encode the form and 'copy' it to the DB via vba.
Thanks.

 

[cyrilbrd]

Thanks for the code, I definitely learned something.
Added to other routines, will test it properly tomorrow morning.
Will get back to this thread for any follow up.

 

[cyrilbrd]

I will post a new thread, Same setup but this time I would like to ADD specific fields so the running total would be updated accordingly.

 

[cyrilbrd]

Good day, Given the same setup with slight variations:
Form is unchanged:

Book1.xlsx
	A	B	C	D	E	F	G
1	Form filled up and printed by user
2
3	desig1	1
4	desig2	2
5	desig3	3
6	desig4	4
7
8	tbl1		field1	field2	field3	field4
9	Type1		5	6	7	8
10	Type2		9	10	11	12
11	Type3		13	14	15	16
12	Type4		17	18	19	20
13	Type5		21	22	23	24
14
15	tbl2	field5	field6	field7	field8	field9	field10
16	Type6	25	26	27	28	29	30
17	Type7	31	32	33	34	35	36
18	Type8	37	38	39	40	41	42
form

The database however was modified as follows:

Book1
	A	B	C	D	E	F	G
1	desig1	desig2	desig3	desig4	Type	Cat	Values
2	1	2	3	4	Type1	field1	5
3	1	2	3	4	Type2	field1	9
4	1	2	3	4	Type3	field1	13
5	1	2	3	4	Type4	field1	17
6	1	2	3	4	Type5	field1	21
7	1	2	3	4	Type1	field2	6
8	1	2	3	4	Type2	field2	10
9	1	2	3	4	Type3	field2	14
10	1	2	3	4	Type4	field2	18
11	1	2	3	4	Type5	field2	22
RawDB

The modification allows better extraction.
I tried to recall the data using a simple formula such as:

=IFERROR(INDEX(RawDB!$G$2:$G$11,MATCH(1,($A9=RawDB!$E$2:$E$11)*(C$8=RawDB!$F$2:$F$11),0)),"")

But I get a DEBUG message for the following:

    WS.Range("C9").Formula = "=IFERROR(INDEX(RawDB!$G$2:$G$11,MATCH(1,($A9=RawDB!$E$2:$E$11)*(C$8=RawDB!$F$2:$F$11),0)),"")"

I was hoping to be able to recall the info from the table using this kind of formula but am wondering what I am doing wrong.
Thanks for any input.

 

[cyrilbrd]

addendum posted in a different thread to improve visibility and feedback.