Link to home
Start Free TrialLog in
Avatar of techdrive
techdriveFlag for United States of America

asked on

Excel Macro Question

Good day everyone i was wondering if I could get some assistance. I am working with excel and have some cells in a column with the values below. Of this one column  EX: C1 in each row could vary as far as the number of delimited items. If I could I would like a macro that will append to the last item in this column C1-105 and I could be prompted or manually type in a entry to append to this column, thanks


C1-C105
one group; doe, john; jane, mary; two group
onegroup;another person; onemoreperson; evenmoreperson
ditto
ditto
ditto
ditto
Avatar of Phillip Burton
Phillip Burton

Don't know what you want - please explain more clearly.
Avatar of Rob Henson
Assuming you have a list of names in column A, the following in C2 will concatenate the names as they increase:

=C1&"; "A2

Copy down as required and then copy further as entries are added to column A.

Thanks
Rob H
Avatar of techdrive

ASKER

User generated imageUser generated image
I will try to explain a different way. The first file is before what I have "before-presentlywhat I have.JPG". I presently have a column full of data seperated by a semi-colon. I wanted to automate adding another name and appending this after the last entry in the column. I have noted what the finished file should look like with the name "need-the-macro-to-do.JPG". Please let me know if this provides some clarity.
Sub AddNewgroup
Introw = 1
Do until cells(introw, 3) =""
  cells(introw, 3) = cells(introw, 3) & ";newgroup"
  introw = introw + 1
Loop
End sub
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In B2 enter value required to be added to values in C. Then in D put formula:

=C2&$B$2&";"

Copy down as required. Then copy and paste values into C.


Repeat with new value in B2 as required.
Select the cells you want to add group to and run, addgrouptoselectedcells
Select the cells you want to remove group from and run, removegroupfromselectedcells


Option Explicit

Const groupdelimiter As String = ";"

Sub addgrouptoselectedcells()
    Dim newgroupname As String
    newgroupname = InputBox("New group name", "Append new group", "New Group")
    addgroup Selection, newgroupname
End Sub

Sub removegroupfromselectedcells()
    Dim groupname As String
    groupname = InputBox("Group name", "Remove group", "Group name")
    removegroup Selection, groupname
End Sub

Sub addgroup(ByRef groupstoappend As Range, ByVal newgroupname As String)
    Dim itercell As Range
    For Each itercell In groupstoappend
        itercell.Value = itercell.Value2 & IIf(Len(itercell.Value2) > 0, groupdelimiter, "") & newgroupname
    Next itercell
End Sub

Sub removegroup(ByRef groupstoremovefrom As Range, ByVal groupname As String)
    Dim itercell As Range
    For Each itercell In groupstoremovefrom
        itercell.Value = Replace(itercell.Value2, groupname & groupdelimiter, "")
        itercell.Value = Replace(itercell.Value2, groupdelimiter & groupname, "")
        itercell.Value = Replace(itercell.Value2, groupname, "")
        itercell.Value = Replace(itercell.Value2, groupdelimiter & groupdelimiter, "")
    Next itercell
End Sub

Open in new window