Okay, a bit of context.

First I changed the lookup percentages so that it was ordered by the year number, 1 2, etc.

Then I created an Excel defined name for the first cell of that data, A94, and called it Percentages.

My code design is
- to go down column A, and where there is a year number, look that up against the data in row 1 so that we know which column to start allocating the percentages
- get the quarter number from column B to add the value for the year to get the exact start column
- check the amount to see which of the percentage rows to lookup and also how many to allocate across (4,8,12, etc.)
- then loop the current row from start column for number of columns, allocating the percentage

[vba]

With ActiveSheet
[/vba]

everything will be done to the activesheet

[vba]

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To iLastRow
[/vba]

calculate the last non-empty row (start at the bottom and work up) and then tun a loop from row 4 to this last row

[vba]

If .Cells(i, "A").Value <> "" Then

iStartCol = Application.Match(.Cells(i, "A").Value, .Rows(1), 0) _
+ .Cells(i, "B").Value - 1
[/vba]

if columns A is not empty, match that value against row 1 to get a start column number and add the quarter number as its start offset

[vba]

Select Case .Cells(i, "D").Value
Case Is < 50000:
cNumCols = 4
iStartRow = Range("Percentages").Row
Case Is < 100000:
cNumCols = 8
iStartRow = Range("Percentages").Row + 2
Case Is < 150000:
cNumCols = 12
iStartRow = Range("Percentages").Row + 4
Case Is < 200000:
cNumCols = 16
iStartRow = Range("Percentages").Row + 6
End Select
[/vba]

then test the value in column D and determine which row of percentages to use and how many to allocate across

[vba]

k = 1
For j = 1 To cNumCols
If .Cells(i, iStartCol + k - 1).Column Mod 5 <> 4 Then
.Cells(i, iStartCol + k - 1).Value = .Cells(i, "D").Value * .Cells(iStartRow, j + 5).Value
Else
.Cells(i, iStartCol + k - 1).FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
j = j - 1
End If
k = k + 1
[/vba]

then run an inner loop to calculate the amount per percentage using the percentage row we derived earlier, and the number of columns of percentages we allocated (the Mod 5 test is because of the embedded totals, we don't wan't to populate those with a calculated amount, but we do want to create totals - the Else). The k counter is because it is not a contiguous range of cells we populate 

					Next j
            End If
        Next i
    End With
close out the loops and the With