+ Reply to Thread
Results 1 to 7 of 7

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

  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:

    Please Login or Register  to view this content.
    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

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

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

    How do you mean to populate the new row? I can't tell from what you have put up.

    The code below does the find-and-insert part properly - but again, I don't know what you want to do exactly with the new row.


    Also, you never showed what you were searching for in the code, I put that in for "Other Project" in red. Also, I added a range variable to handle the found cell.


    Please Login or Register  to view this content.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    05-21-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    39

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

    Hi!

    Thanks for your help so far, got it to check and push things down. A bit more detailed:

    What I meant with populate is that I have about 21 columns of data that get extracted from another workbook (Hence didn't write it earlier... forgot to mention it, sorry ). So once the relevant project is found, all the previous data is pushed down and each cell of the row gets the new information (in the sample it would be,first the project, then the version (+1) and then the city.All of the data may or may not be the same). All of this data is stored in vba variables and copied from the original projects workbook.
    The tool is part of a controlling mechanism to check the development of costs and such things. Thus first search for projects and add one to the version of the workbook
    If the project isn't found then it gets inserted at the top so that it can be used as a visual cue for a follow up.

    The updated code that I have gets stuck at "c.Offset(0,2).Value = St_Location" (runtime error 438):

    Please Login or Register  to view this content.
    Again, many thanks in advance!!! Do let me know if you find that I left something out

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

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

    The only thing I see in that block of code that looks odd right-off is: c = St_Project

    Replace that with c.value = St_Project - Possibly that is the issue? I there is nothing wrong with c.Offset(0, 2).Value = St_Location that I can see.

  5. #5
    Registered User
    Join Date
    05-21-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    39

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

    I have modified the c = St_Project. When there is no string it works fine now (Great stuff! Thanks!), only need to figure out how to keep the formatting of row 2 instead of that from row 1.
    However the error 438 (Object doesn't support this property or method) persists... I have changed he format of the cell to be Text (in case it had to do wih ST_Location being declared as a string...). Ideas?

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

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

    You can assign the value of a cell a string variable all day long - no idea why that's giving you an issue. Can you post up your code/sample project?

  7. #7
    Registered User
    Join Date
    05-21-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    39

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

    Hi!

    Was a few days unexpectedly away... I have now managed to tackle the problem by going line by line... Problem was that "c" was being assigned a string if the variable was found... Solution:


    Please Login or Register  to view this content.
    Many thanks for helping me out with figuring out how to do the search!!

+ Reply to Thread

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