Hey all,
Ive found some code on a forum elsewhere and tried to retrofit it solve a problem im facing with formatting.
I need to insert a certain amount of blank rows based on a value. I.E: If the value is 3, i need to insert 3 blank rows below this cell. The code ive found works nicely, but when it gets to 0 it doesnt work. Can anyone cast your eye over it and let me know if you find anything that may help.
Current Code:
Input:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day/Night Shift[/TD]
[TD]Date Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/2/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/3/2018[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/3/2018[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Desired output:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Day/Night Shift[/TD]
[TD]Date Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/2/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/3/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/3/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]
Ive found some code on a forum elsewhere and tried to retrofit it solve a problem im facing with formatting.
I need to insert a certain amount of blank rows based on a value. I.E: If the value is 3, i need to insert 3 blank rows below this cell. The code ive found works nicely, but when it gets to 0 it doesnt work. Can anyone cast your eye over it and let me know if you find anything that may help.
Current Code:
Sub InsertRowsIf()
Dim lr As Long, R As Range, i As Long
lr = Range("C" & Rows.Count).End(xlUp).Row
Set R = Range("C1", "C" & lr)
Application.ScreenUpdating = False
For i = R.Rows.Count To 1 Step -1
If IsNumeric(R.Cells(i, 1).Value) And Not IsEmpty(R.Cells(i, 1)) Then
R.Cells(i, 1).Offset(1, 0).Resize(R.Cells(i, 1).Value).EntireRow.Insert
End If
Next
End Sub
Input:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day/Night Shift[/TD]
[TD]Date Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/2/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/3/2018[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/3/2018[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Desired output:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Day/Night Shift[/TD]
[TD]Date Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/2/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/2/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]1/3/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD]1/3/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]