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 [url removed, login to view](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 [url removed, login to view](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 [url removed, login to view] 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”.
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.
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.
9 freelanceria on tarjonnut keskimäärin 166 $ tähän työhön
I am not random bidder.. I hope I can do this one and I do not outsource the work.. Please ping me if you are interested with my proposal and thank you..