Results 1 to 7 of 7

Searching text and if found then insert row above, if not then insert row at top

Threaded View

kurifodo Searching text and if found... 10-28-2015, 01:16 PM
GeneralDisarray Re: Searching text and if... 10-28-2015, 02:27 PM
kurifodo Re: Searching text and if... 10-29-2015, 05:40 AM
GeneralDisarray Re: Searching text and if... 10-29-2015, 09:38 AM
kurifodo Re: Searching text and if... 10-29-2015, 10:00 AM
GeneralDisarray Re: Searching text and if... 10-29-2015, 11:00 AM
kurifodo Re: Searching text and if... 11-04-2015, 10:10 AM
  1. #1
    Registered User
    Join Date
    05-21-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    39

    Searching text and if found then insert row above, if not then insert row at top

    Hi everyone!

    I am having trouble figuring out how to use the method .Find... What I am trying to do is search column("A:A") for the first instance of a string contained in a variable. If found then insert a row above it pushing everything down) and populate that row with Data (Value in column B tells you the version... so version that got pushed down +1). If it doesn't find anything then insert a row at 2 (pushing everything else down) an populate it.

    I have managed so far:

    
    Sub Eckdatenblatt()
    
    Application.ScreenUpdating = False
    Dim FindRow As Range
    Dim St_Project As String
    Set EW_ECK = Workbooks.Open("S:\Eckdatenblatt\Eckdatenblatt.xlsx")
    Set Summary = EW_ECK.Sheets("Summary")
    Summary.Activate
    
    With Summary
        
        Columns("A:A").Select
        
        Selection.Find(What:=St_Project, After:=ActiveCell, LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        ActiveCell.Rows("1:1").EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "=R[1]C+1"
        ActiveCell.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    End With
    
    EW_ECK.Close SaveChanges:=True
    
    Application.ScreenUpdating = True
    End Sub
    The problem is that the rows get shifted down and then work is being carried out below the data instead of on top...
    In the example attached: If user searched for Project "Other Project", then shift everything down from row 5 and add one to the version
    It also crashes if the string is not found and I am not sure how to handle the error and start at the top (row 2) with a fresh line and version 1.
    The code gets executed from a different file where all the data is contained.

    Many Thanks!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Look for specific text string, insert row below, insert text to that row
    By jwats in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2015, 03:08 PM
  2. [SOLVED] Insert two slashes if not found
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-06-2015, 01:57 PM
  3. [SOLVED] VBA codes to insert the rows and copy the first entire row text and insert sheet
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2013, 05:05 AM
  4. [SOLVED] Insert 2 new lines after same text is found
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2013, 05:15 PM
  5. Insert Row where value not found
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2012, 03:32 PM
  6. Insert Row For Value Not Found
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-14-2009, 01:07 PM
  7. found and insert
    By sungpeng in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2007, 01:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1