VBA Nested Loop for Cost Allocation

[KDBrown]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CC1
[/TD]
[TD]FUNC_1
[/TD]
[TD].75
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]CC1
[/TD]
[TD]FUNC_2
[/TD]
[TD].25
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]CC2
[/TD]
[TD]FUNC_1
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]CC1
[/TD]
[TD]600000
[/TD]
[TD]100.00
[/TD]
[TD][/TD]
[TD]CC1
[/TD]
[TD]600000
[/TD]
[TD]FUNC_1
[/TD]
[TD]0.75
[/TD]
[TD]75.00
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]CC1
[/TD]
[TD]610000
[/TD]
[TD]200.00
[/TD]
[TD][/TD]
[TD]CC1
[/TD]
[TD]600000
[/TD]
[TD]FUNC_2
[/TD]
[TD]0.25
[/TD]
[TD]25.00
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]CC2
[/TD]
[TD]700000
[/TD]
[TD]300.00
[/TD]
[TD][/TD]
[TD]CC1
[/TD]
[TD]610000
[/TD]
[TD]FUNC_1
[/TD]
[TD]0.75
[/TD]
[TD]150.00
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CC1
[/TD]
[TD]610000
[/TD]
[TD]FUNC_2
[/TD]
[TD]0.25
[/TD]
[TD]50.00
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CC2
[/TD]
[TD]700000
[/TD]
[TD]FUNC_1
[/TD]
[TD]1.00
[/TD]
[TD]300.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[KDBrown]

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