Solved

Excel Macro Question

Posted on 2014-11-24
7
66 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 31

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 31

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

744 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

12 Experts available now in Live!

Get 1:1 Help Now