i have range of cells sheet1.range ("E12:E19") containing numbers like 510,411,300,100,80. Sheet1.cell("O6") contains the maximum a number can be say 100. what i want to do is , Divide the numbers in Sheet1.range("E12:E19") into equalish whole parts such that none of the resulting number is greater than 100 and print resulting numbers in Sheet2 starting from sheet2.range("G31")
[TABLE="width: 200"]
<tbody>[TR]
[TD]Shee1.Marker[/TD]
[TD]Plies[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]411[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
will become
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet2 Marker[/TD]
[TD]Plies[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]83[/TD]
[/TR]
</tbody>[/TABLE]
..... and so on
i have managed to divide it into equal parts but still can't figure out how to make last Row in above table as 82+1 instead of 82 this is the code i have used
[TABLE="width: 200"]
<tbody>[TR]
[TD]Shee1.Marker[/TD]
[TD]Plies[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]411[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
will become
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet2 Marker[/TD]
[TD]Plies[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]83[/TD]
[/TR]
</tbody>[/TABLE]
..... and so on
i have managed to divide it into equal parts but still can't figure out how to make last Row in above table as 82+1 instead of 82 this is the code i have used
Code:
Dim i, lays, max, q, j, filled, remaind As Integer
Dim lrlastrow As Long
lrlastrow = Range("A" & Rows.Count).End(xlUp).Row
max = 100
filled = 1
For i = 12 To 19
' end the loop if lays end
If Range("E" & i).Value = "" Then
End
Else
If Range("E" & i).Value <= max Then
Sheet2.Range("B" & 30 + filled).Value = filled 'number
Sheet2.Range("C" & 30 + filled).Value = Sheet1.Range("D" & i).Value 'marker
Sheet2.Range("G" & 30 + filled).Value = Sheet1.Range("E" & i).Value 'plies
filled = filled + 1
Else
q = Range("E" & i).Value
lays = 1
Do While q >= max
q = Range("E" & i).Value \ lays
remaind = (Range("E" & i).Value Mod lays)
lays = lays + 1
Loop
Range("R" & i).Value = lays - 1
' printing the lays
For j = 1 To lays - 1
Sheet2.Range("B" & 30 + filled).Value = filled 'number Sheet2.Range("C" & 30 + filled).Value = Sheet1.Range("D" & i).Value 'marker
Sheet2.Range("G" & 30 + filled).Value = q 'plies
filled = filled+1
Next j
End If
End If
Next i
End sub