Hello and thank you for any attention my post may receive.
My goal is to:
[TABLE="class: grid, width: 20"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]area
[/TD]
[TD]type
[/TD]
[TD]frequency
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]264a
[/TD]
[TD]a
[/TD]
[TD]daily
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]260d
[/TD]
[TD]a
[/TD]
[TD]weekly
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]264a
[/TD]
[TD]c
[/TD]
[TD]monthy
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]273c
[/TD]
[TD]e
[/TD]
[TD]daily
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]264b
[/TD]
[TD]c
[/TD]
[TD]weekly
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]273a
[/TD]
[TD]b
[/TD]
[TD]daily
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]264a
[/TD]
[TD]d
[/TD]
[TD]daily
[/TD]
[/TR]
</tbody>[/TABLE]
The code I currently have finds the first occurrence, inserts 6 full rows above, then fills those six rows with the data that was above the occurrence. It is rather back to front and I am at lost as to where to make further changes to achieve my goal.
Thanks again in advance, and have a great day!
My goal is to:
- find every occurrence of the word "Weekly" in column C e.g. C3,C6;
- insert 6 lines below (in range A2:C500) each occurrence;
- fill each occurrence of those 6 blank lines with the data from range A2:C500 where the word "Weekly" was found.
[TABLE="class: grid, width: 20"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]area
[/TD]
[TD]type
[/TD]
[TD]frequency
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]264a
[/TD]
[TD]a
[/TD]
[TD]daily
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]260d
[/TD]
[TD]a
[/TD]
[TD]weekly
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]264a
[/TD]
[TD]c
[/TD]
[TD]monthy
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]273c
[/TD]
[TD]e
[/TD]
[TD]daily
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]264b
[/TD]
[TD]c
[/TD]
[TD]weekly
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]273a
[/TD]
[TD]b
[/TD]
[TD]daily
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]264a
[/TD]
[TD]d
[/TD]
[TD]daily
[/TD]
[/TR]
</tbody>[/TABLE]
The code I currently have finds the first occurrence, inserts 6 full rows above, then fills those six rows with the data that was above the occurrence. It is rather back to front and I am at lost as to where to make further changes to achieve my goal.
Code:
Sub InsertRows()
Cells.Find(What:="Weekly", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim d As Integer
d = Range("C:C").End(xlDown).Row
Dim c As Range
For i = d To 1 Step -1
If Cells(i, 3).Value = "Weekly"Then
Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = 6
If Rng = "" Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.insert 'Shift:=xlDown
k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 4).End(xlToLeft).Column
Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
End If
Next
End Sub
Thanks again in advance, and have a great day!
Last edited: