Solved

Excel / Unique ID

Posted on 2010-08-14
8
308 Views
Last Modified: 2012-05-10
Hi Experts,

I have tried to obtain code off the net and make changes to it, but nothing is working. I have a userform
that works great for entering data into sheet 1. I was using a basic formula in column L to concatenate fields together to make a unique ID. When I copy this formula down it then makes the userform write to the wrong row in sheet 1 when data is entered.
I'm wondering is it possible to automatically concatenate column (E, C, and K) together using VBA according to when the rows of data are entered. Is there some kind of change event in VBA that can achieve this in column L? Is it possible to have it automatically do this without any user interference or button etc.

Many Thanks in advance, I have attached a sample sheet in excel.
SampleUniqueID.xlsx
0
Comment
Question by:JKCC
  • 3
  • 3
  • 2
8 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 33438649
JKCC,

The file does not contain a UserForm nor does it contain any VBA code. Please upload your file with both the UserForm and the code you are currently using - albeit with errors.

Patrick
0
 

Author Comment

by:JKCC
ID: 33438693
I can't because it is confidential information. That is why I created a simple dummy attached with a very basic sample. If it is not possible than I will have to look for other ways of achieving this. I'm just really after column L concatenating the 3 fields together as the rows are populated. E.g. as row one is populated than, column L should populate with the concatenation and so on through VBA. Is it that this has to be in the userform or can it be a separate module specificually for sheet1? I currently do the basica formula in sheet1 and not on the userform.

I'm new to all of this and have had lots of help getting this done so I'm still learning ALOT!!!!

Thanks heaps!

0
 

Author Comment

by:JKCC
ID: 33438702
Can it be based on according to column A. So when column A is populated, then column L would generate the concatenation via VBA in the corresponding row etc? I dunno, just a thought. I'm not specifically after a unique ID being generated just the count down and the concatenatin via vba. Hope this helps a little. Anythig to get me started would be great.

Cheers,
0
 
LVL 80

Expert Comment

by:byundt
ID: 33438711
You can use a worksheet event sub that will populate column L without a formula. This sub must be placed in the code pane for the worksheet, and won't work at all anywhere else.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range, rw As Range, targ As Range

Dim n As Long

n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 'Number of last row with data

Set targ = Union([C2].Resize(n - 1, 1), [E2].Resize(n - 1, 1), [K2].Resize(n - 1, 1))

Set targ = Intersect(targ, Target)

If targ Is Nothing Then Exit Sub



Application.EnableEvents = False

For Each rw In targ.EntireRow.Rows

    With rw

        If Application.CountA(.Cells(3), .Cells(5), .Cells(11)) = 3 Then

            .Cells(12) = Left(.Cells(5), 2) & .Cells(3) & .Cells(11)

        Else

            .Cells(12) = ""

        End If

    End With

Next

Application.EnableEvents = True

End Sub

Open in new window

SampleUniqueIDQ26403446.xlsm
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Accepted Solution

by:
patrickab earned 250 total points
ID: 33438735
JKCC,

The code below is in the attached file. When a change is made in column 1 of Sheet1, the formula you have specified is placed automatically on the same row in column L.

Patrick
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim celle As Range

With Sheets("Sheet1")
    Set rng = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    If Not Intersect(rng, Target) Is Nothing Then
        .Cells(Target.Row, 12).FormulaR1C1 = "=CONCATENATE(LEFT(RC[-7],3),"""",RC[-9],RC[-1])"
    End If
End With

End Sub

Open in new window

SampleUniqueID-01.xls
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 33438751
Although it didn't make any difference with your sample data, I had the wrong number of characters from column E. It should have been 3 in statement 13.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range, rw As Range, targ As Range

Dim n As Long

n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 'Number of last row with data

Set targ = Union([C2].Resize(n - 1, 1), [E2].Resize(n - 1, 1), [K2].Resize(n - 1, 1))

Set targ = Intersect(targ, Target)

If targ Is Nothing Then Exit Sub



Application.EnableEvents = False

For Each rw In targ.EntireRow.Rows

    With rw

        If Application.CountA(.Cells(3), .Cells(5), .Cells(11)) = 3 Then

            .Cells(12) = Left(.Cells(5), 3) & .Cells(3) & .Cells(11)

        Else

            .Cells(12) = ""

        End If

    End With

Next

Application.EnableEvents = True

End Sub

Open in new window

SampleUniqueIDQ26403446.xlsm
0
 

Author Comment

by:JKCC
ID: 33439410
Thanks experts...both solutions work great.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33439754
JKCC - Thanks for the grade and points - Patrick
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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

14 Experts available now in Live!

Get 1:1 Help Now