View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Norman Jones Norman Jones is offline
external usenet poster
 
Posts: 5,302
Default find text then insert new row above

Hi Nick,

Tried it but nothing happens


The code works for me in my test workbook.

What happens if you try to find "Last Risk Above" manually?
Is this text present in Column B?
Where is the code placed?

---
Regards,
Norman



"Nick Smith" wrote in message
...
Hi Norman,

Tried it but nothing happens. Made some amends as below, but still
nothing.
Not even an error. Any ideas as I am confused!

Sub InsertNewRiskInStatusReport()

Dim SH As Worksheet
Dim rng As Range
Const sStr As String = "Last Risk Above"

Set SH = ThisWorkbook.Sheets("Status Report") '<<==== CHANGE

Set rng = SH.Columns("B:B").Find(What:=sStr, _
After:=Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
With rng
.EntireRow.Insert
.Offset(-2).Copy
.Offset(-1).PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End With
End If
End Sub
'<<=============