Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Unique ID to end of every cell?
Hi, I have a spreadsheet with about 10 columns and 50
rows. I upload the spreadsheet to test an application and need to modify the spreadsheet each time to make it different. What I would like is to be able to add a unique number or a letter at the end of the data currently in Column C. So if Column C now is (Adam, Steve, Roger) I would like to make it AdamA, SteveB, RogerC. Is there a way to macro this somehow? what is the formula? I could not get auto fill to work. thanks in advance if you can help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Unique ID to end of every cell?
Kevin,
Something like this Private Sub Worksheet_Change(ByVal Target As Range) Dim myId As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target If .Value < "" Then On Error Resume Next myId = Evaluate(ThisWorkbook.Names("___UniqueId").RefersT o) myId = myId + 1 ThisWorkbook.Names.Add Name:="___UniqueId", RefersTo:="=" & myId .Value = .Value & "_" & myId End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Kevin McKellar" wrote in message ... Hi, I have a spreadsheet with about 10 columns and 50 rows. I upload the spreadsheet to test an application and need to modify the spreadsheet each time to make it different. What I would like is to be able to add a unique number or a letter at the end of the data currently in Column C. So if Column C now is (Adam, Steve, Roger) I would like to make it AdamA, SteveB, RogerC. Is there a way to macro this somehow? what is the formula? I could not get auto fill to work. thanks in advance if you can help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Unique ID to end of every cell?
Option Explicit
Sub append_id() Dim iNumRows As Integer, iCount As Integer, c As Variant iNumRows = Sheets("sheet1").UsedRange.Rows.Count iCount = 0 For Each c In Range("C1", "C" & iNumRows) c.Value = c.Value & iCount iCount = iCount + 1 Next End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filer for unique records and return all column data for unique rec | Excel Discussion (Misc queries) | |||
How to pick out unique components in a list with unique and common | Excel Discussion (Misc queries) | |||
unique values in a cell base on another cell | Excel Discussion (Misc queries) | |||
a unique cell value returns multiple cell values from another shee | Excel Worksheet Functions | |||
Formulas for...1. Counting unique cells 2. Display unique contents | Excel Programming |