Ifs, creating repetition and VBA

[seller7]

Thank you for the wonderful responses

[6StringJazzer]

Is there any reason this needs to use VBA? Here is a formula solution.

Values as displayed

	A	B	C	D	E	F	G	H	I
1			Start	0	101	201	301	401	501
2			End	100	200	300	400	500	600
3	Number	Repeat Across	Amount
4	39	1	2,000	2000
5	34	5	346	346.2	346.2	346.2	346.2	346.2
6	359	2	9,994				9994	9994
7	102	3	7,218		7218	7218	7218
8	405	2	5,995					5995	5995

Underlying formulas

	A	B	C	D	E	F	G	H	I
1			Start	0	101	201	301	401	501
2			End	100	200	300	400	500	600
3	Number	Repeat Across	Amount
4	39	1	2000	=IF(AND($A4>=D$1,$A4<=D$2),$C4,"")	=IF(AND($A4>=E$1,$A4<=E$2),$C4,IF(AND($A4<=E$2,COUNTIF($D4:D4,">0")<$B4),$C4,""))	=IF(AND($A4>=F$1,$A4<=F$2),$C4,IF(AND($A4<=F$2,COUNTIF($D4:E4,">0")<$B4),$C4,""))	=IF(AND($A4>=G$1,$A4<=G$2),$C4,IF(AND($A4<=G$2,COUNTIF($D4:F4,">0")<$B4),$C4,""))	=IF(AND($A4>=H$1,$A4<=H$2),$C4,IF(AND($A4<=H$2,COUNTIF($D4:G4,">0")<$B4),$C4,""))	=IF(AND($A4>=I$1,$A4<=I$2),$C4,IF(AND($A4<=I$2,COUNTIF($D4:H4,">0")<$B4),$C4,""))
5	34	5	346.153846153846	=IF(AND($A5>=D$1,$A5<=D$2),$C5,"")	=IF(AND($A5>=E$1,$A5<=E$2),$C5,IF(AND($A5<=E$2,COUNTIF($D5:D5,">0")<$B5),$C5,""))	=IF(AND($A5>=F$1,$A5<=F$2),$C5,IF(AND($A5<=F$2,COUNTIF($D5:E5,">0")<$B5),$C5,""))	=IF(AND($A5>=G$1,$A5<=G$2),$C5,IF(AND($A5<=G$2,COUNTIF($D5:F5,">0")<$B5),$C5,""))	=IF(AND($A5>=H$1,$A5<=H$2),$C5,IF(AND($A5<=H$2,COUNTIF($D5:G5,">0")<$B5),$C5,""))	=IF(AND($A5>=I$1,$A5<=I$2),$C5,IF(AND($A5<=I$2,COUNTIF($D5:H5,">0")<$B5),$C5,""))
6	359	2	9993.5	=IF(AND($A6>=D$1,$A6<=D$2),$C6,"")	=IF(AND($A6>=E$1,$A6<=E$2),$C6,IF(AND($A6<=E$2,COUNTIF($D6:D6,">0")<$B6),$C6,""))	=IF(AND($A6>=F$1,$A6<=F$2),$C6,IF(AND($A6<=F$2,COUNTIF($D6:E6,">0")<$B6),$C6,""))	=IF(AND($A6>=G$1,$A6<=G$2),$C6,IF(AND($A6<=G$2,COUNTIF($D6:F6,">0")<$B6),$C6,""))	=IF(AND($A6>=H$1,$A6<=H$2),$C6,IF(AND($A6<=H$2,COUNTIF($D6:G6,">0")<$B6),$C6,""))	=IF(AND($A6>=I$1,$A6<=I$2),$C6,IF(AND($A6<=I$2,COUNTIF($D6:H6,">0")<$B6),$C6,""))
7	102	3	7217.93076923077	=IF(AND($A7>=D$1,$A7<=D$2),$C7,"")	=IF(AND($A7>=E$1,$A7<=E$2),$C7,IF(AND($A7<=E$2,COUNTIF($D7:D7,">0")<$B7),$C7,""))	=IF(AND($A7>=F$1,$A7<=F$2),$C7,IF(AND($A7<=F$2,COUNTIF($D7:E7,">0")<$B7),$C7,""))	=IF(AND($A7>=G$1,$A7<=G$2),$C7,IF(AND($A7<=G$2,COUNTIF($D7:F7,">0")<$B7),$C7,""))	=IF(AND($A7>=H$1,$A7<=H$2),$C7,IF(AND($A7<=H$2,COUNTIF($D7:G7,">0")<$B7),$C7,""))	=IF(AND($A7>=I$1,$A7<=I$2),$C7,IF(AND($A7<=I$2,COUNTIF($D7:H7,">0")<$B7),$C7,""))
8	405	2	5995	=IF(AND($A8>=D$1,$A8<=D$2),$C8,"")	=IF(AND($A8>=E$1,$A8<=E$2),$C8,IF(AND($A8<=E$2,COUNTIF($D8:D8,">0")<$B8),$C8,""))	=IF(AND($A8>=F$1,$A8<=F$2),$C8,IF(AND($A8<=F$2,COUNTIF($D8:E8,">0")<$B8),$C8,""))	=IF(AND($A8>=G$1,$A8<=G$2),$C8,IF(AND($A8<=G$2,COUNTIF($D8:F8,">0")<$B8),$C8,""))	=IF(AND($A8>=H$1,$A8<=H$2),$C8,IF(AND($A8<=H$2,COUNTIF($D8:G8,">0")<$B8),$C8,""))	=IF(AND($A8>=I$1,$A8<=I$2),$C8,IF(AND($A8<=I$2,COUNTIF($D8:H8,">0")<$B8),$C8,""))

[beyond Excel]

Hi. A solution with VBA:

Sub Macro1()
Dim mCell As Range, Rng As Range
Set Rng = Range("D1", Range("D1").End(xlToRight))
For Each mCell In Range("A4", Range("A3").End(xlDown))
  Cells(mCell.Row, Rng(WorksheetFunction.Match(mCell, Rng, 1)).Column).Resize(, mCell(, 2)) = mCell(, 3)
Next
End Sub