using VBA code/macro for InputBox and then performing calculations

[saadshakil]

I have data set of about 6000 rows and it looks something like below:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: center"]Cost Center[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85, align: center"]Item code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl66, width: 81, align: center"] Value ($)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl66, width: 100, align: center"] Sales Proceeds[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74, align: center"]Proportionate Sales proceeds[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]2587[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: center"]2,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]4152[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: center"]4,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S1111[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]9687[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]2,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S0014[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]2560[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 10,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S1005[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]2154[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 4,500[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S1005[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]6781[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 6,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]3078[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 3,500[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]5548[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 4,200[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]8623[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 5,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]9012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 9,800[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Lets assume Column A - Cost Center is a unique Location. For eg. HK1110S1111 is Room A, HK1110S0014 is Room B and so on. Each room has Items in it which have a unique code - Column B. Column C represents the Value of items in Column B.

1) Firstly, I need to enter Sales Proceeds(D). For that cells in Column D needs to be merged according to value in Column A. Sales Proceeds are cumulative - according to Location, not Item(B) wise. Therefore D2:D4, D6:D7 and D8:D11 needs to be merged based on their correspondence values in Column A. I have already written and assigned a Command Button for this VBA code and the cells in Column D are merging perfectly.

Now I need something like InputBox which would display text from Column A and the punched valued is entered in Column D. For eg. Upon hit of Command button InputBox pops up like: "Enter Sales proceeds for HK1110S1111" and the input value is entered in correspondence cell of A2 that is D2:D4 (merged cells). The task should repeat for all Column A range (Enter Sales proceeds for HK1110S0014, Enter Sales proceeds for HK1110S0005) and so on. I would assign a Command Button to this task as Step 2 for my own reference.

2) This is Step 3 and like above I would insert a Command button for this macro/code. Here I need to do calculations in Column E. This is very critical and most important part. The Sales Proceeds (D) should be distributed to Items(B) according to proportion of their Value(C). For eg. Sales Proceeds for HK1110S1111 is $12,000 (D2:D4). Thereby:
E2 = 2000(C2)/(2000(c2)+4000(c3)+2000(c4))*12000(D2:D4)
E3 = 4000/(2000+4000+2000) * 12000
E4 = 2000/(2000+4000+2000) * 12000

Sales Proceeds for HK1110S0014 is $9,000 so:
E5 = 10000/10000 * 9000

I would really appreciate your help to automate these calculation. There are about 6000 rows and more to come and this calculation has to be performed on each row. So far I have been doing all of this manually which is resulting in mistakes and lot of time consuming.

The end result should look something like this:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: center"]Cost Center[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85, align: center"]Item code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl66, width: 81, align: center"] Value ($) [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl66, width: 100, align: center"] Sales Proceeds [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74, align: center"]Proportionate Sales proceeds[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]2587[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: center"]2,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]12,000[/TD]
[TD="align: center"]3,000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]4152[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: center"]4,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]"[/TD]
[TD="align: center"]6,000[/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S1111[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]9687[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]2,000[/TD]
[TD="align: center"]"[/TD]
[TD="align: center"]3,000[/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S0014[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]2560[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 10,000[/TD]
[TD="align: center"]9,000[/TD]
[TD="align: center"]9,000[/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S1005[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]2154[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 4,500[/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"]4286[/TD]
[/TR]
[TR]
[TD="align: center"]HK1110S1005[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]6781[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 6,000[/TD]
[TD="align: center"]"[/TD]
[TD="align: center"]5714[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]3078[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 3,500[/TD]
[TD="align: center"]45,000[/TD]
[TD="align: center"]7,000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]5548[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 4,200[/TD]
[TD="align: center"]"[/TD]
[TD="align: center"]8,400[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]8623[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 5,000[/TD]
[TD="align: center"]"[/TD]
[TD="align: center"]10,000[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84, align: center"]HK1110S1117[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85, align: center"]9012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"] 9,800[/TD]
[TD="align: center"]"[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]


Looking forward for solutions. Thanks in advance.