VBA Nested Loop for Cost Allocation

KDBrown

New Member
Joined
Jan 11, 2010
Messages
14
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
Joined
Jan 11, 2010
Messages
14
Bump.
Any assistance or links to similar VBA code will be appreciated! Thank you!
 

Forum statistics

Threads
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.
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
Top