EXCEL VBA PROJECT (SIMPLE QUESTIONNAIRE COLLECTION)

Hi, good day ! Thanks for viewing my project. This VBA macro is work on Excel2010 and later. Details is as follows. [VBA Macro Overview] 1. Input source data is [login to view URL](this is sample input file) which includes user’s results. From A1 to D2 is question ID, and the following rows is user’s answer. 2. Final output data is [login to view URL](this is sample output file). 3. This purpose of macro is for automating routine work as below procedure. [VBA Macro Procedure] 1. User can select any file which includes user’s results with dialog, and used as input source data. 2. Macro procedure is executed as following STEP 1 to 8. 3. After completion of STEP 1 to 8, Input source data is save as new output file. At the time, user can select output file path with dialog. ----------------------------------------------------------------------------------- STEP 1 BUILD TABLE FOR VLOOKUP Sheet “vlookup_data” is generated from [login to view URL] which is already inputted prepared.(from cell B24 is name, from cell D24 is number) This sheet is used at STEP 3. STEP 2 BUILD TABLE FOR VALUE CONVERSION Sheet “score”　is generated from VBA macro, and all value is fixed in VBA code. This purpose of this table is, several question ID is needed value conversion at STEP 5. STEP 3 INSERT NEW ROW FOR NAME Insert new row at column ”A”, and input “name” using vlookup with key "user id"(Column B). STEP 4 COPY ORIGINAL DATA Copy original data (from Column D to Column BH) to BJ. STEP 5 VALUE CONVERSION Execute value conversion at Columns BJ to BP (Question A1-A7) Columns BT to BV (Question A11-A13) Columns BX (Question A15) Columns CA to CC (Question B1-B3) with using vlookup from sheet “score”. Example) 1 -> 4 2 -> 3 3 -> 2 4 -> 1 STEP 6 SCORE CALUCULATION Column DO(SCORE 1) is sum value from Column CA to Column DC(Question B1-B29). If score is 77 point or over, cell background and font color is RED using conditional formatting. Column DP(SCORE 2) is sum value from Column BJ to Column BZ(Question A1-A17), and Column DD to Column DL(Question C1-C9). If score is 76 point or over, cell background and font color is GREEN using conditional formatting. STEP 7 MARKING CONDITION 1) If column DO cell is RED, input “TRUE” in column DQ cell. CONDITION 2) If column DP cell is GREEN and value of DO cell is 63 and over, input “TRUE” in column DQ cell. CONDITION 3) If except value (from 1 to 4) is inputted from column BJ to column DL(Question A1-C9), input “ERROR” in column DQ cell. Example) 1|3 -> ERROR Blank -> ERROR STEP 8 CATEGORIZE CLASS CONDITION 1) Column DO cell color is RED, and DP cell color is GREEN, input “4” in column DR cell. CONDITION 2) Column DO cell color is RED, and DP cell color is not GREEN, input “2” in column DR cell. CONDITION 3) Column DO cell color is not RED, and DP cell color is GREEN, and DQ cell value is “TRUE”, input “3” in column DR cell. CONDITION 4) DQ cell value is “ERROR”, input “5” in column DR cell. CONDITION 5) Except of CONDITION 1 to 4, input “1” in column DR cell.