using VBA code/macro for InputBox and then performing calculations

S

saadshakil

New Member
Joined
Jul 22, 2018
Messages
1
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

Ivan F Moala
  • Question Question
ViperGTS Using the FEQUENCY function??
Replies
3
Views
2K
Hero-0952
H

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top