+ Reply to Thread
Results 1 to 15 of 15

Need Macro To Add 2 Blanks Cells Below Cell with words.

  1. #1
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Need Macro To Add 2 Blanks Cells Below Cell with words.

    REPLACE ENGINE OIL & FILTER, VEHICLE INSPECTION, FILL FLUIDS
    RESET THE OIL REPLACEMENT REMINDER LIGHT.
    (blank row )
    (blank row )
    E


    I have an excel file with many columns containing information at the end of each column like this.

    It lists car verbage, then 2 blank cells are required , followed by an E.

    I was wondering if a macro could be used to where it looks at a column, finds where the verbage ends, and automatically inserts 2 blank cells below it followed by an E. In other words it needs to make sure only 2 blank cells are after the verbage and an e should follow in the cell below. If the column has 3 blank cells below the verbage it should fix it and add the E at the appropriate space.

    Thanks

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    Does this work for you?

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    Does this work for you?

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    Cells(1, Col).End(xlDown).Offset(3, 0) = "E"

    Highlights the code here and says run time error, debugger went into effect.


    Do i have to change something here?

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    The code works fine for me, I would need to see your workbook.

  6. #6
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    how can we arrange that, should i private message you an email?

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    You can use the manage attachments button to upload

    Click Post Reply, scroll down the page to find the button

  8. #8
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    Here is the worksheet, i deleted alot of the columns and have only 2 listed.


    I have about 7 to 8 tabs like this with 40 to 50 columns


    If you look at row 19 In column B, the verbage is being represented from a hlookup and is being pulled from the verbage tab.

    I manully write 7 rows with the hlooup formula and copy those 7 and paste it in every column. So everycolumn has the new hlookup formula and is pulling correctly from the legend in ROW 19 ( Example RPV, RPVA )

    Once i do that , the verbage is pulled correctly and i have to have 2 blank cells followed by capital E in the 3rd cell. So for example the macro would know column b ends at row 21, put nothing in row 22, and 23, and put a capital E in row 24 on the far left side.

    The next column happens to end in the same spot but some will have more verbage end on row 23, or 24 depending how long the verbage is in the verbage tab.

    IT would be nice if a macro could first copy the formulas in the first column ( i only have 3 rows with the forumla here, but i do 7 in the real file . So imagine row 19 to 25 with a hlookup formula. If a macro would copy those 7 rows and paste it in every column next to the legend. Because if you notice its not every column. Its B and E in this example. So its after every 2 columns really starting with b.

    Once the formula is pasted, if i could run another macro would would automatically put those E's after 2 blank cells.

    Basically i know this can be automated very easily, and i hope you can help.

    I tried to be clear as i could, its harder exlaining it in text then showing it.

    Thanks
    Attached Files Attached Files
    Last edited by donnydorko; 01-27-2009 at 06:11 PM.

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    This works on your example, you need to put in the lookup to remove the #N/A

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    I ran the macro but i didnt produce the results i was looking for,

    I included another sheet here to show you exactly what i needed,

    You can use the original worksheet to test out any macro to see if it matches.

    Thanks for all the help
    Attached Files Attached Files

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    Where is the data from? For some reason the code is not finding the correct last cell in the colum. Is the data imported, there might be hidden carriage returns that could be interfering.l

  12. #12
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    not sure what you mean by that, the data is being presented from a hlookup from the verbage tab, and i have no clue what hidden carriage returns are . I seriously doubt there is any of that.

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    \For some reason the simple code to find the next empty cell is not working, it only works when I manually select all cells below the E & clear the contents.

    Also some columns have two rows of verbiage, not 3.
    Last edited by royUK; 01-29-2009 at 07:39 AM.

  14. #14
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    yea thats what i was saying the verbage is different, its pulling from a hlookup, sometimes it might have 6, sometimes 3, sometimes 2, the E's wont be in the same row in otherwords, b/c it will depend on how long the verbage is.

  15. #15
    Forum Contributor
    Join Date
    01-25-2009
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    177

    Re: Need Macro To Add 2 Blanks Cells Below Cell with words.

    ohh well thanks anyways, going to try to contact some other people for a solution. Seems like this could be automated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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