Solved

Excel Macro Question

Posted on 2014-11-24
7
68 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:techdrive
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40462134
Don't know what you want - please explain more clearly.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40462493
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
0
 

Author Comment

by:techdrive
ID: 40462770
C--Users-388822-Pictures-account-before-C--Users-388822-Pictures-account-need-th
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40463167
Sub AddNewgroup
Introw = 1
Do until cells(introw, 3) =""
  cells(introw, 3) = cells(introw, 3) & ";newgroup"
  introw = introw + 1
Loop
End sub
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40463171
Please make sure that you have saved your spreadsheet beforehand, as the changes are not undo able.

The word newgroup should be Newgroup in the code above.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40463268
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.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40472277
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

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now