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:
<tbody>
</tbody>
What results should show:
<body id="************" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody>
</tbody></body>
<tbody>
</tbody>
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.
Original:
| Customer | Balance | Interest Rate | Status | State |
| 35001 | $300.00 | 1.00% | 10 | CA |
| 10001 | $500.00 | 1.25% | 7 | CA |
| 10356 | $300.00 | 1.50% | 7 | CA |
| 10686 | $800.00 | 1.75% | 7 | NY |
| 12369 | $100.00 | 1.15% | 10 | CA |
| 3068900 | $(200.00) | 1.35% | 6 | NY |
| 3697431 | $(100.00) | 1.75% | 7 | NY |
| 3897921 | $(300.00) | 1.00% | 10 | CA |
| 3196780 | $(100.00) | 1.25% | 7 | CA |
| 3976451 | $(200.00) | 1.15% | 10 | NY |
<tbody>
</tbody>
What results should show:
<body id="************" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
| Customer | Balance | Interest Rate | Status | State |
| 35001 | $300.00 | 1.00% | 10 | CA |
| 10001 | $500.00 | 1.25% | 10 | CA |
| 13356 | $300.00 | 1.50% | 7 | CA |
| 15686 | $800.00 | 1.75% | 7 | NY |
| 12369 | $100.00 | 1.15% | 10 | CA |
| 3068900 | $(200.00) | 1.35% | 6 | NY |
| 3697431 | $(100.00) | 1.75% | 7 | NY |
| 3897921 | $(300.00) | 1.00% | 10 | CA |
| 3196780 | $(100.00) | 1.25% | 7 | CA |
| 3976451 | $(200.00) | 1.15% | 10 | NY |
<tbody>
</tbody>
| Customer | Balance | Interest Rate | Status | State |
| 35001 | $300.00 | 1.00% | 10 | CA |
| 10001 | $500.00 | 1.25% | 7 | CA |
| 10356 | $300.00 | 1.50% | 7 | CA |
| 10686 | $800.00 | 1.75% | 7 | NY |
| 12369 | $100.00 | 1.15% | 10 | CA |
| State | $1000.00 | 1.55% | 7 | |
| National | $4000.00 | 1.15% | 10 | |
| 3068900 | $(200.00) | 1.35% | 6 | NY |
| 3697431 | $(100.00) | 1.75% | 7 | NY |
| 3897921 | $(300.00) | 1.00% | 10 | CA |
| 3196780 | $(100.00) | 1.25% | 7 | CA |
| 3976451 | $(200.00) | 1.15% | 10 | NY |
<tbody>
</tbody>
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.
