# VBA Nested Loop for Cost Allocation

#### KDBrown

##### New Member
Good day all.
I'm using Excel 2007 and am working on my first attempt at a Loop. I've spent alot of time looking at other posts. I believe I understand the individual concepts but can't seem to put it all together. It's time to ask for assistance.

This is an allocation loop.

Rows 5-7 contain a table of organizations (ex. CC1) and the percentages allocated to various functions (ex. FUNC_1). So in this example, CC1 is allocated 75% to FUNC_1 and 25% to FUNC_2. The range name is "Percents". This table will remain relatively static over time (may see minor changes/additions).

Rows 17-19 (cols A-C) contain cost data for the organizations by account. In this example, CC1 has \$100 on account 600000. This data will expand and contract on a monthly basis.

Rows 17-21 (cols E-I) shows the desired result. The loop has taken each line of cost data and allocated it to the functions based on the Percents table. So in this example, the first row of cost data (CC1, acct 600000, \$100) is now split into two lines (one for FUNC_1 at 75% of \$100 and one for FUNC_2 at 25% of \$100).

 A B C D E F G H I 5 CC1 FUNC_1 .75 6 CC1 FUNC_2 .25 7 CC2 FUNC_1 1.00 17 CC1 600000 100.00 CC1 600000 FUNC_1 0.75 75.00 18 CC1 610000 200.00 CC1 600000 FUNC_2 0.25 25.00 19 CC2 700000 300.00 CC1 610000 FUNC_1 0.75 150.00 20 CC1 610000 FUNC_2 0.25 50.00 21 CC2 700000 FUNC_1 1.00 300.00

<tbody>
</tbody>

Here's my code. It works successfully on the first pass of each line of cost data but doesn't properly bring back any additional lines (in other words I don't get rows 18 & 20). I believe it needs some sort of counter or NextRow (tried and failed). I also need help on generating the value in col I (everything I try brings back an error).

Sub AllocationV6()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 17 To FinalRow
For y = 5 To 7
If Cells(y, 1) = Cells(x, 1) Then
Cells(x, 5).FormulaR1C1 = "=RC[-4]"
Cells(x, 6).FormulaR1C1 = "=RC[-4]"
Cells(x, 7).FormulaR1C1 = "=VLOOKUP(RC[-6],Percents,2,False)"
Cells(x, 8).FormulaR1C1 = "=VLOOKUP(RC[-7],Percents,3,False)"
End If
Next y
Next x
End Sub

I hope I've explained the situation without being too long winded. Your assistance is greatly appreciated!

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### KDBrown

##### New Member
Bump.
Any assistance or links to similar VBA code will be appreciated! Thank you!

Replies
28
Views
396
Replies
2
Views
121
Replies
2
Views
229
Replies
0
Views
65
Replies
26
Views
1K

1,137,353
Messages
5,680,995
Members
419,948
Latest member
Sbakker1

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

### Which adblocker are you using?

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

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