+ Reply to Thread
Results 1 to 7 of 7

Code needed for adding text onto current text every three rows down a column?

  1. #1
    Registered User
    Join Date
    09-20-2017
    Location
    Erie,PA, USA
    MS-Off Ver
    MS Office 2016
    Posts
    7

    Red face Code needed for adding text onto current text every three rows down a column?

    I have a list of names where the name is repeated 3 times and I need to add text after the second and the third repetition of the name. My Excel currently looks as following:
    Morgan
    Morgan
    Morgan
    Smith
    Smith
    Smith

    I would like it to look like this:
    Morgan
    Morgan (ECI)
    Morgan (HO)
    Smith
    Smith (ECI)
    Smith (HO)

    I'm still new to VBA and coding in general, I tried to write a code for this to add the (ECI) text to start and it didn't work. Any guidance on what I've done wrong and how to fix it would be greatly appreciated.

    This is the code I tried to write.

    Sub AddText_ECI()
    Range("E3").Select
    Do Until IsEmpty(ActiveCell)
    ActiveCell = Range("E2").Offset(3, 0) + " (ECI)"
    ActiveCell.Offset.Select
    Loop
    End Sub

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code needed for adding text onto current text every three rows down a column?

    Hi,

    You don't need a macro. As simple formula will do. e.g in B1 copied down,

    =IF(COUNTIF(A$1:A1,A1)=2,A1&" (ECI)",IF(COUNTIF(A$1:A1,A1)=3,A1&" (HO)",A1))
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-20-2017
    Location
    Erie,PA, USA
    MS-Off Ver
    MS Office 2016
    Posts
    7

    Re: Code needed for adding text onto current text every three rows down a column?

    Thanks for your response, that does work great. However, I tried to do it using code so I could try to start improving my VBA skills. So I was really hoping for some guidance on making this work with a VBA code not coding within a cell. I'm trying to use this as a learning opportunity rather than just using the simplest way to reach my goal, if that makes sense.

    Thanks!

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code needed for adding text onto current text every three rows down a column?

    Hi,

    If I was coding this I'd simply make the macro write the formula I gave you into a helper column, and then copy the helper column and paste it back to the original column as values and delete the helper column

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Code needed for adding text onto current text every three rows down a column?

    Maybe:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-20-2017
    Location
    Erie,PA, USA
    MS-Off Ver
    MS Office 2016
    Posts
    7

    Re: Code needed for adding text onto current text every three rows down a column?

    Thank you so much this worked perfectly!

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Code needed for adding text onto current text every three rows down a column?

    You're welcome. Glad to help out and thanks for the feedback and the rep.

+ 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. Periodically adding additional text to current formula
    By HCornelian1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-09-2017, 09:28 AM
  2. VBA code to delete rows if column V contains certain text
    By Declamatory in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2016, 12:20 PM
  3. [SOLVED] Adding Current month and year to VBA generated text box
    By akhileshgs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2014, 03:32 AM
  4. Replies: 1
    Last Post: 02-24-2014, 10:48 PM
  5. [SOLVED] Formula Needed to auto populate current date when text entered into a cell
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2013, 09:50 PM
  6. [SOLVED] Macro code to show only the rows with specific cell text in a particular column
    By Itachi1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2013, 01:33 PM
  7. Replies: 11
    Last Post: 03-04-2013, 12:57 PM

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