Ok, I think I am looking at this the wrong way, so I need some guidance from the more knowledgeable.
I created a data collection workbook.
Page 1 of the workbook is data entry, where a data entry person can enter about 50 cells of information. This data then fills in several formulas, and outputs a single row of data in 80 columns.
Through a VBA script, this row is then copied to the data sheet when the "record" macro button is pressed. it identifies the last row number, adds the data collected to the next row and increments the row count, clears about a dozen of the data entry points on the data entry page, and puts the user back in the first data entry cell.
The idea being that a data entry person would read paper, data enter, and collect information, move to next entry. This information is then used elsewhere in bulk.
I have just been handed a spreadsheet containing 10,000 records that have come to us from a partner. The columns of data entry are all there and map one to one, but the columns of calculation are not.
I am fairly advanced with excel formulas, and fumble/cut-paste/butcher VBA.
I see two approaches:
If I was working back in my terminal days, I would use ANZIO Lite to mimic data entry and have it loop through the records. I would think someone smarter than me could create a macro/script that would loop through my data entry sheet in a similar way.
I could add the formulas from my data entry page into columns bypassing the data entry page. Rebuilding what is a page worth of formulas into a single row per record. Since I am more comfortable with excel formulas, I would probably end up doing it this way.
Does anyone have a VBA script that "mimics" data entry into a form that would save me hours or suggestions?
Ken
I created a data collection workbook.
Page 1 of the workbook is data entry, where a data entry person can enter about 50 cells of information. This data then fills in several formulas, and outputs a single row of data in 80 columns.
Through a VBA script, this row is then copied to the data sheet when the "record" macro button is pressed. it identifies the last row number, adds the data collected to the next row and increments the row count, clears about a dozen of the data entry points on the data entry page, and puts the user back in the first data entry cell.
The idea being that a data entry person would read paper, data enter, and collect information, move to next entry. This information is then used elsewhere in bulk.
I have just been handed a spreadsheet containing 10,000 records that have come to us from a partner. The columns of data entry are all there and map one to one, but the columns of calculation are not.
I am fairly advanced with excel formulas, and fumble/cut-paste/butcher VBA.
I see two approaches:
If I was working back in my terminal days, I would use ANZIO Lite to mimic data entry and have it loop through the records. I would think someone smarter than me could create a macro/script that would loop through my data entry sheet in a similar way.
I could add the formulas from my data entry page into columns bypassing the data entry page. Rebuilding what is a page worth of formulas into a single row per record. Since I am more comfortable with excel formulas, I would probably end up doing it this way.
Does anyone have a VBA script that "mimics" data entry into a form that would save me hours or suggestions?
Ken
