• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert Blank Rows Above & Below On Partial Text Founded Cell

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 .

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

APPLICATION DEFINE ERROR.jpg


On This Line

dont work.jpg

I can not figure out what excel want?.

I also attached my working file here

hope your co-operation.

Regards,

Chirag Raval
 

Attachments

  • booking dispatch.xlsm
    224.9 KB · Views: 5
Last edited:
Hi !

As selecting is useless so Selection too,
as always just directly work with your object variable
instead of a bad Select use …

Resize(1 is useless too as it's exactly the same of source range object …

Directly use Rows collection to insert any row
without Resize as it seems you have something weird in your worksheet.
And insert first under and after above the found cell.

Notice your loop logic fails as first address is not good once rows inserted !
So better work with row # instead of address …
 
Dear Sirs @Marc L & Sir @Chihiro

first point is that each block of party in attached file there are merged cells in each party's block. that may be raise trouble to run loop as cell identifier.

should there are must revers loop logic due to each loop will insert 2 rows above and 2 rows below the founded cell?

I use find method due to it can access part of string in find. like wild card
"*" word "*" .

I already try on as use "like" operator & "Instr" as below

Code:
Sub InsertRowBasedsOnCel6ok()
Dim cell As Range
Application.ScreenUpdating = False

For Each cell In ActiveSheet.Range("H900:H5000")
If InStr(LCase(cell.Value), LCase("DESPATCH JAN TO")) > 0 Then

cell.Resize(1, 0).EntireRow.Insert shift:=xlDown
cell.Offset(1, 0).EntireRow.Insert shift:=xlDown
    End If
Next
End Sub

But only find method is faster then any loop i prefer this.

how can I construct that with find method?

Regards,

Chirag Raval
 

As written in post #3 the issue not comes from Find method
but from your syntax how you use Resize statement !

And again you can directly use Rows instead of Resize.EntireRow
like when you activate the Macro Recorder and operate manually …
 
Dear Sir @Marc L,

As per your instruction change in code,
its working but weirdly. its insert many rows seems infinite loop and inserting rows, its can't stop

you can just check my this code in my attached file.

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.Rows.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

Is there need to loop last to first row?
how to construct reverse loop (last row to 2)?



please help
Regards,

Chirag Raval
 
Dear Sirr @Marc L

On Each loop , range expanded as rows inserted.

Find method's search direction if use xlprevious after range "H1"
Then it can helpful as go from last to top. As your point that first found
Cell's location become useless for further process ,
Should I use this approach? (Xlprevious)?

Is there any need to construct as below?
(1) find as xlprevious
(2) loop backward (last to top).

If I use row (row no) instead of found.address
I should change on every found cell.row as below?
Cell(found,8).insert.rows(2) (insert 2 rows below)
But how to insert 2 rows above?

Sorry for not construct right Syntex.

Please help.

Regards,

Chirag Raval
 
Rows must replace Selection like you insert rows manually
using the Macro Recorder …

The issue is not the Find SearchDirection
but just your first row or address logic fails !
For exemple if first address is H20, after inserting a couple of rows before
so what is the new address of data in previous cell H20 ?
 
Dear Sir @Marc L,

Thank you very much for reply.

I modify as per you instruction (except firstfound.address)
its successfully working on every founded cell & insert 2 rows above founded cell but problem to insert 2 rows below(as testing purpose, sorry till I take base of "Activecell). suppose focus stay on founded cell , how to insert 2 rows below?

Also there are problem to clarify first found.address. & loop goes to infinite.

how can resolve this both aspects?

Code

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.Activate
              ActiveCell.Rows.Resize(2, 1).EntireRow.Insert shift:=xlDown
              Range(ActiveCell, ActiveCell.Rows.Offset(2, 1)).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

please help.

Regards,

Chirag Raval
 
Last edited:
No need to focus but just to precise where the insertion must be …

For infinite loop the answer was yet here :
if first address is H20, after inserting a couple of rows before
so what is the new address of data in previous cell H20 ?
So on a new workbook, just try manually:
- enter "123" in H19 and "ABC" in H20
- insert a couple of rows between row #19 & row #20 :
what is the cell new address of content "ABC" ? At child level …
 
Dear Sir @Marc L

As per your guide line I make target last row number & got half Success,(just can insert 2 rows above) based on fixed last row address as below to overcome loop goes to infinite.

If you want to check below code on my attached file, please rectify want to find word or string which should be find "*DESPATCH*JAN*" OR / "*DISPATCH*JAN*")

Code:
Option Explicit
Option Compare Text
'HALF SUCESS AS VERY HARD TRY
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 Long
    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 = "*DESPATCH*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.Row + 2
            Do
              Found.Activate
'              ActiveCell.Rows.Resize(2, 1).EntireRow.Insert Shift:=xlDown
'              Range (ActiveCell.Offset(2, 1).Activate
                Rows(Found.Row & ":" & Found.Row + 1).EntireRow.Insert Shift:=xlDown
'              ActiveCell.Offset(2, 1).EntireRow.Insert Shift:=xlDown
'              Selection.Offset(1, 0).EntireRow.Insert shift:=xlDown
                Set Found = .FindNext(Found)
            Loop While Not Found Is Nothing And Found.Row <> firstfound
        End If
    End With
    Set Found = Nothing
    End Sub

Please guide how to insert 2 rows below founded cell?

Regards,

Chirag Raval
 
Again EntireRow is useless with Rows like any Select or Activate
Do Loop just needs to compare with firstfound
as Found can't be Nothing inside the If block …

From Found position use for example Offset to choose the place to insert
- like yet shown in a thread of yours with all range features -
and as yet written before to insert any row before Found position …
Or if you prefer after so just calculate the right place to insert …
 
Dear Sir @Marc L,

Yes perfectly I can insert 2 rows below & 2 rows above on founded cell.

First Found Row successfully stored in variable to stop the infinite loop.

As per your suggestion , remove "nothing" Portion from loop.

As per your suggestion, remove activate, select from code.

I use offset & resize , (resize for how many rows need to insert below founded row).

& macro stop at right point after done its job.

Code:
Option Explicit
Option Compare Text
'HALF SUCESS AS VERY HARD TRY
'Sub InserDelRowsBasedonCelFndMethodok()
Sub InserlRowsBaseonlFndMethodSUCok()
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 Long 'BECAUSE 1ST ROW NUMBER REQUIRE TO DEFINE LAST STAGE OF LOOP
    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)
                firstfound = Found.Row + 2
            Do
'              
                Rows(Found.Row & ":" & Found.Row + 1).Insert shift:=xlDown
                Rows(Found.Row).Offset(1, 0).Resize(2).Insert shift:=xlDown
             
                Set Found = .FindNext(Found)
            Loop While Found.Row <> firstfound
'        End If
    End With
    Set Found = Nothing
    End Sub


Can you have any more precise or another approach to do this job with find method?

Or how can I do this job as use with another approach with find method?

Request to help to some focus on points on this macro which I can modify

Regards,

Chirag Raval
 
As Find is an approach itself …

You define sht variable to use it only once
but you use several times ActiveSheet

You delete the If condition but it is necessary !
As I just wrote about the Loop

Mod of gWORD constant to include DESPATCH typo :​
Code:
Option Compare Text
Option Explicit

Sub InserlRowsBaseonlFndMethodSUCok()
     Const gWORD = "(D?SPATCH JAN *"
       Dim Found As Range
       Dim firstfound As Long  ' BECAUSE 1ST ROW NUMBER REQUIRE TO DEFINE LAST STAGE OF LOOP
        ActiveSheet.UsedRange   ' Refresh UsedRange
        Set Found = ActiveSheet.UsedRange.Columns(8).Find(what:=gWORD, LookIn:=xlValues, LookAt:=xlPart)
    If Not Found Is Nothing Then
            firstfound = Found.Row + 2
            Application.ScreenUpdating = False
        Do
            Rows(Found.Row + 1 & ":" & Found.Row + 2).Insert shift:=xlShiftDown
            Rows(Found.Row & ":" & Found.Row + 1).Insert shift:=xlShiftDown
            Set Found = ActiveSheet.UsedRange.Columns(8).FindNext(Found)
        Loop Until Found.Row = firstfound
            Application.ScreenUpdating = True
            Set Found = Nothing
    End If
End Sub
 
Dear Sir @Mark L,

Great optimization.
Oh , super, you set found in 1 line but with multiple parameters & direct necessary
targeted range (Columns(8) ,

I really seen now & here that how to practically experience with use of wildcards like "?",
As it use when you not sure about spelling or arranged string within to be find that's also great.

Yes you are right , procedures only start when "If not found is nothing" .

Really learnable that how to work on around environment based on founded cell
As every process construct on core element "founded cell,'s row or column, or use of offset.

( If you don't mine, can I ask why not use "resize" on founded cell?).

May be for stop macro's loop goes to infinite, and macro stop at proper pont, approach of use "loop while " required 2 conditions or
2 check points.

(1) if found variable filled with or assigned valuable in it ,must be loop continued.
(2) after complete procedures on all founded cells , anf find method repeat from first found, & goes to infinite
so must be stop search when it ancounter with first found address. So found.row <>first found.

But your approach to use "loop until" require only 1 condition or checkpoint.
Question in my mind that should it not necessary to use first checkpoint as
Not found is nothing in "do until " loop?.

Great learning, really feels the power & speed of find method compare with for each loop.

Can you have any link about why find method is so fast? ( Just for learn).or
Some learnable / practicable examples of use of find method's megic?

Can also there are link found about other things of excel that can work on many things
Without loop like find method do? Like intersect works , like ctrl+shift+enter not require
Loop on range?

Thank you very much for your effort till right solution of this thread.

Regards,

Chirag Raval
 
Last edited:
why not use "resize" on founded cell?
As Resize is the issue with your initial attachment 'cause of your worksheet,
maybe a formatting like merged cells for example …
I tried same initial code on a worksheet I created : no issue !

Can you have any link about why find method is so fast?
As Find is the search Excel inner feature : often inner features are faster
as compiled code than any VBA interpreted code …
 
Dear Sir @Marc L ,

Thank you very much sir..
yes you are right ...merged cells is not for real database and not for VBA.
its seems just user's visual fashion purpose or print output looks good for reader.

But I confused about loop method/types.

Code:
‘Loop Method 1
            Do
              ‘Do Process Here, Whatever You Want
                Set Found = .FindNext(Found)
            Loop While Not Found Is Nothing And Found.address <>

‘Loop Method 2

       Do
          ‘Do Process Here, Whatever You Want
            Set Found = ActiveSheet.UsedRange.Columns(8).FindNext(Found)
        Loop Until Found.Row = firstfound

which should be used?

Please guide.

Regards,

Chirag Raval
 

It depends on the data & the purpose context.
Here just pure logic : within the If statement at the beginning
Found can't be nothing anymore …​
 
Back
Top