已完成

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 [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”.

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.

技能: Excel Macros, Excel VBA

查看更多: excel vba programming examples download, excel vba project ideas, 2500 excel vba examples free download, excel vba projects free download, excel macro examples pdf, excel vba project examples, vba excel examples pdf, excel vba projects for practice, simple excel vba project, create help file excel vba project, sample excel vba project, beginning excel vba project, create excel vba project, online excel vba project india, excel vba project description, excel vba project schedule, 1st excel vba project, finalize excel vba project, search excel vba csv simple database, fun excel vba project

关于此雇主:
( 20个评论 ) Tokorozawa, Japan

项目ID: #15861672

授予:

MimiFL

Automating Questionnaire Collection as described. Basically creating a macro (Excel 2010 or later - it will work even in lower versions), that will import 2 files (input and lookup file) and generate an output file as 更多

$88 USD 在0天内
(7条评论)
4.1

9 威客就此工作平均出价 $166

schoudhary1553

Greetings, I can help you in EXCEL VBA PROJECT. Please ping me for more discussion. Please see my profile i have deliver almost 280 projects with 100% client satisfaction. I have more than 5 years of experienc 更多

$150 USD 在6天内
(9条评论)
4.5
aubrey03

I am an Excel VBA expert. I specialize in process automation. I can create complex macros and formulas which I believe is necessary for this project.

$60 USD 在2天内
(11条评论)
4.4
vmadhavan15

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..

$140 USD 在3天内
(8条评论)
4.0
jasalaz100

Immediate availability. Responsible, fast and accurate. Full-time freelancer. Excel expert. 100% satisfaction guaranteed. Available to any help after completion. Experience of 6 years using excel for organizing an 更多

$111 USD 在10天内
(4条评论)
2.7
zu02

Hi, there I hope all's well with you. I see you'r looking for a developer proficient in Ms Excel/VB. Really all my projects here I've developed for Ms Access/Excel/VB projects. I viewed your requirement and 更多

$555 USD 在7天内
(2条评论)
2.8
$150 USD 在3天内
(1条评论)
2.3
abhinovpankaj

Hello, I have been working as freelancer since last ~ 5 years,prior to this was working with a MNC as an Sr. Software Professional. Below are some key areas of my skills: -- VBA and Excel,Word,Office Applicat 更多

$166 USD 在6天内
(1条评论)
2.2
caabdul

Hi there, I am Excel VBA expert and can do this job with perfection. Thank you

$70USD 在1天里
(0条评论)
0.0