[VBA QUESTION] Add 2 lines into a table at a certain location and fill in other fields

B

bnbcat

New Member
Joined
Apr 27, 2016
Messages
41
Office Version
  1. 365
Platform
  1. Windows
I have data like so, in which I need to use VBA to add 2 lines that will always be added after the 5-digit customer number. Additionally, I need to fill in certain fields for those 2 new items, based on the data of the already existing customer accounts.

Original:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Balance[/TD]
[TD="align: center"]Interest Rate[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]State[/TD]
[/TR]
[TR]
[TD]35001[/TD]
[TD="align: right"]$300.00[/TD]
[TD]1.00%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD="align: right"]$500.00[/TD]
[TD]1.25%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10356[/TD]
[TD="align: right"]$300.00[/TD]
[TD]1.50%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10686[/TD]
[TD="align: right"]$800.00[/TD]
[TD]1.75%[/TD]
[TD]7[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]12369[/TD]
[TD="align: right"]$100.00[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3068900[/TD]
[TD="align: right"]$(200.00)[/TD]
[TD]1.35%[/TD]
[TD]6[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]3697431[/TD]
[TD="align: right"]$(100.00)[/TD]
[TD]1.75%[/TD]
[TD]7[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]3897921[/TD]
[TD="align: right"]$(300.00)[/TD]
[TD]1.00%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3196780[/TD]
[TD="align: right"]$(100.00)[/TD]
[TD]1.25%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3976451[/TD]
[TD="align: right"]$(200.00)[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD]NY[/TD]
[/TR]
</tbody>[/TABLE]

What results should show:
<body id="************" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Balance[/TD]
[TD="align: center"]Interest Rate[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]State[/TD]
[/TR]
[TR]
[TD]35001[/TD]
[TD="align: right"]$300.00[/TD]
[TD]1.00%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD="align: right"]$500.00[/TD]
[TD]1.25%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]13356[/TD]
[TD="align: right"]$300.00[/TD]
[TD]1.50%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]15686[/TD]
[TD="align: right"]$800.00[/TD]
[TD]1.75%[/TD]
[TD]7[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]12369[/TD]
[TD="align: right"]$100.00[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3068900[/TD]
[TD="align: right"]$(200.00)[/TD]
[TD]1.35%[/TD]
[TD]6[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]3697431[/TD]
[TD="align: right"]$(100.00)[/TD]
[TD]1.75%[/TD]
[TD]7[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]3897921[/TD]
[TD="align: right"]$(300.00)[/TD]
[TD]1.00%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3196780[/TD]
[TD="align: right"]$(100.00)[/TD]
[TD]1.25%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3976451[/TD]
[TD="align: right"]$(200.00)[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD]NY

[/TD]
[/TR]
</tbody>[/TABLE]
</body>
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Balance[/TD]
[TD="align: center"]Interest Rate[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]State[/TD]
[/TR]
[TR]
[TD]35001[/TD]
[TD="align: right"]$300.00[/TD]
[TD]1.00%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD="align: right"]$500.00[/TD]
[TD]1.25%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10356[/TD]
[TD="align: right"]$300.00[/TD]
[TD]1.50%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]10686[/TD]
[TD="align: right"]$800.00[/TD]
[TD]1.75%[/TD]
[TD]7[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]12369[/TD]
[TD="align: right"]$100.00[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD="align: right"]$1000.00[/TD]
[TD]1.55%[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD="align: right"]$4000.00[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3068900[/TD]
[TD="align: right"]$(200.00)[/TD]
[TD]1.35%[/TD]
[TD]6[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]3697431[/TD]
[TD="align: right"]$(100.00)[/TD]
[TD]1.75%[/TD]
[TD]7[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]3897921[/TD]
[TD="align: right"]$(300.00)[/TD]
[TD]1.00%[/TD]
[TD]10[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3196780[/TD]
[TD="align: right"]$(100.00)[/TD]
[TD]1.25%[/TD]
[TD]7[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]3976451[/TD]
[TD="align: right"]$(200.00)[/TD]
[TD]1.15%[/TD]
[TD]10[/TD]
[TD]NY
[/TD]
[/TR]
</tbody>[/TABLE]


The "State" and "National" rows are the new rows that need to be added. The balances will be manually entered. However, the interest rate for "State" should be the weighted average of the interest rates of accounts starting with "10" and the interest rate for "National" should be the interest rate of the account starting with "12." The Status should have the same status as accounts starting with "10" and the Status for "National" should have the same status as accounts starting with "12." State will remain blank.

Any help will be greatly appreciated.
 
You must log in or register to reply here.

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