Chirag R Raval
Member
Dear Sir,
I trying to insert 2 rows above and 2 rows below on founded cell on partial base search with find method.
but it raise error .
Its raise error as per below screen shot
On This Line
I can not figure out what excel want?.
I also attached my working file here
hope your co-operation.
Regards,
Chirag Raval
I trying to insert 2 rows above and 2 rows below on founded cell on partial base search with find method.
but it raise error .
Code:
Option Explicit
Option Compare Text
Sub InserDelRowsBasedonCelFndMethodok()
Dim gWORD As String 'remember here give Exact Word to find iif it with space then put space
Dim sht As Worksheet
Dim Found As Range
Dim firstfound As String
Dim LastColumn As Long
Dim lastrow As Long
Dim MYRNG As Range
Set sht = ActiveSheet
' Refresh UsedRange
ActiveSheet.UsedRange
' Find Last Row
lastrow = ActiveSheet.UsedRange.Rows.Count
' Select Range
Set MYRNG = sht.Range("A1:I" & lastrow)
gWORD = "DISPATCH JAN 17"
With MYRNG
Set Found = MYRNG.Cells.Find(what:=gWORD, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
firstfound = Found.address
Do
Found.Select
Selection.Resize(1, 0).EntireRow.Insert shift:=xlDown
Selection.Offset(1, 0).EntireRow.Insert shift:=xlDown
Set Found = .FindNext(Found)
Loop While Not Found Is Nothing And Found.address <> firstfound
End If
End With
Set Found = Nothing
End Sub
Its raise error as per below screen shot
On This Line
I can not figure out what excel want?.
I also attached my working file here
hope your co-operation.
Regards,
Chirag Raval
Attachments
Last edited: