Insert New Row

[Michael Koerner]

I would like to be able to insert a new row labelled 4-Independent after the
row that is labelled 3-SuperStore in Column "E"

 

[Michael Koerner]

Forgot to add that I would also like whatever is in Columns A, B, C, and D
copied from the row above the new one copied into the new row. Hope this
makes sense

--

Regards
Michael Koerner


I would like to be able to insert a new row labelled 4-Independent after the
row that is labelled 3-SuperStore in Column "E"

 

[Don Guillett]

Sub InsertAfterFind()
What = "3-SuperStore"
newtext = "4-Independent"
With Columns("e").Find(What, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
..Offset(1).EntireRow.Insert
..Offset(1).Value = newtext
End With
End Sub

 

[Don Guillett]

Sub InsertAfterFindCopyRowAbove()
What = "3-SuperStore"
newtext = "4-Independent"
With Columns("e").Find(What, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
..Offset(1).EntireRow.Insert
..Offset(, -4).Resize(, 4).Copy .Offset(1, -4)
..Offset(1).Value = newtext
End With
End Sub

 

[Michael Koerner]

Don;

Thanks very much. How do I make it go through the whole sheet 1400 rows and
make the change each time it hits 3-SuperStore?

--

Regards
Michael Koerner


Sub InsertAfterFindCopyRowAbove()
What = "3-SuperStore"
newtext = "4-Independent"
With Columns("e").Find(What, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
..Offset(1).EntireRow.Insert
..Offset(, -4).Resize(, 4).Copy .Offset(1, -4)
..Offset(1).Value = newtext
End With
End Sub

 

[Don Guillett]

You should have mentioned that in the ORIGINAL post
Use FINDNEXT . See example in vba help index

 

[Michael Koerner]

I thought I did, my mistake for not making it clear. Thanks very much

--

Regards
Michael Koerner



You should have mentioned that in the ORIGINAL post
Use FINDNEXT . See example in vba help index

 

[Michael Koerner]

I looked at the VBA example. But have no idea how to incorporate that into
what you wrote originally.

--

Regards
Michael Koerner



You should have mentioned that in the ORIGINAL post
Use FINDNEXT . See example in vba help index

 

[Don Guillett]

Use this

Sub InsertAfterFindALL()
What = "3-SuperStore"
newtext = "4-Independent"

With ActiveSheet.Columns("e")
Set c = .Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(1).EntireRow.Insert
c.Offset(, -4).Resize(, 4).Copy c.Offset(1, -4)
c.Offset(1).Value = newtext
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End With
End Sub

 

[Michael Koerner]

Don;

Thanks you very much. Worked like a charm

--

Regards
Michael Koerner


Use this

Sub InsertAfterFindALL()
What = "3-SuperStore"
newtext = "4-Independent"

With ActiveSheet.Columns("e")
Set c = .Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(1).EntireRow.Insert
c.Offset(, -4).Resize(, 4).Copy c.Offset(1, -4)
c.Offset(1).Value = newtext
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End With
End Sub