VBA loop to duplicate rows based number of days per month

[mybyby]

Hi guys

I need your help guys to write VBA code to duplicate rows based on number of days per months
I have monthly order number but it is not comparable to daily actuals, what I need is to populate the table into daily file based on number days per month. e.g. I need 31 rows for first data below.

Appreciate your help. Thanks!


[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]EOMDate[/TD]
[TD]OrderQty[/TD]
[TD]Remarks[/TD]
[TD]No of Days per month[/TD]
[TD]DailyTarget (OrderQtyMth/No of days)[/TD]
[/TR]
[TR]
[TD]AAAA[/TD]
[TD]31/1/2017[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl65, width: 67"]17689[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Store1[/TD]
[TD]31[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67, align: right"]570.6129[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]AAAA[/TD]
[TD]28/02/2017[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl65, width: 67"]1,806[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Store1[/TD]
[TD]28[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67, align: right"]64.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]BBBB[/TD]
[TD]31/1/2017[/TD]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]22,029[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Store1[/TD]
[TD]31[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67, align: right"]710.6129[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]BBBB[/TD]
[TD]28/02/2017[/TD]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]2,500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Store1[/TD]
[TD]28[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="width: 67, align: right"]89.28571[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[louisH]

Hi,

In column "No of Days per month":

=DAY(EOMONTH("EOMDate",0))

Replace "EOMDate" with the matching cell

Or if EOMDate is already the end of the month, just do :

=DAY("EOMDate")