VBA dinamic vlookup table array based on if statement

[morjaman]

Hi there!


I will be grateful for some help.
I have WorkBook with 2 sheets in it.


sheet 1
[TABLE="width: 328"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]macro / vlookup result[/TD]
[TD]value1[/TD]
[TD]value2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 11 4 42621[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 11 4 42621[/TD]
[TD="align: right"]272[/TD]
[TD="align: right"]272[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 11 4 42621[/TD]
[TD="align: right"]319[/TD]
[TD="align: right"]319[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 11 4 42621[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 1 42651[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]224[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 1 42651[/TD]
[TD="align: right"]286[/TD]
[TD="align: right"]286[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 1 42651[/TD]
[TD="align: right"]399[/TD]
[TD="align: right"]175[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 2 42651[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 2 42651[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]152[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]J 28 3 42951[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]305[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]J 28 3 42951[/TD]
[TD="align: right"]496[/TD]
[TD="align: right"]496[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]J 28 3 42951[/TD]
[TD="align: right"]1999[/TD]
[TD="align: right"]1175[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]J 36 5 42978[/TD]
[TD="align: right"]496[/TD]
[TD="align: right"]496[/TD]
[/TR]
</tbody>[/TABLE]

sheet2
[TABLE="width: 182"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]J 11 4 42621[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 1 42651[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 13 2 42651[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 21 2 42616[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 25 1 42622[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 25 1 42908[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]J 25 3 42908[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]J 28 3 42951[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]J 36 5 42978[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]J 38 1 42978[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]J 39 3 42985[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
My need is in the 1-st sheet in col.B macro to put formulas vlookup(A:A;sheet2!$A$1:$B$11;2;0)
until Value1 (col.C) > Value2 (col.D) (bolded) then to skip first row in table array vlookup(A:A;sheet2!$A$2:$B$11;2;0) and continue until value in Col.A <> ""

Any help will be appreciated.