Add One to Highest Alphabet in a String

truongn2

New Member
Joined
May 25, 2007
Messages
9
Hi,

I have a UserForm with two text boxes. One is where the user will input their full name and the other one is their initials. When the user click submit, it will write those two fields onto a running list on SHEET2. However, it is possible for people to have the same initials. So to avoid duplicate intials, can there be a macro insert an extra letter at the end of the initial to make it unique? Here is an example:


A B
1 DLJA Darren Lee Johnson
2 DLJB Danielle Lynn Jackson
3


So when a third user with the name of "Derrick Lawson Jacobson" enter their information. A macro would go through the list on SHEET2 and realize that they are already two entries with "DLJ", so it will give "Derrick" the initials of "DLJC".

Thanks in advance for your help,
Ngoc
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What do you want to do when this situation exceeds 26 duplicates?
Can you post the code where it writes it to the sheet?
 

truongn2

New Member
Joined
May 25, 2007
Messages
9
Yes, I understand the 26 letter limit, I would rather use number myself, but I am working a project that is already defined as using letters so I have to work with that. I do not think we will have enough users to exceed the limit.

Here is the code for writing it to SHEET2:
Code:
    ' Select the Section worksheet
    ActiveWorkbook.Sheets("SHEET2").Activate
    Range("A1").Select
    
    
    ' Check for the first empty cell within the A column
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If


    ' When empty cell is found, add the User's entry
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = cbxInitials.Value & "B"
    ActiveCell.Offset(0, 1) = txtFullName.Value


The "B" part in the code is where I would add the incremental letter to the duplicates. I am using "B" for now as an example.

Please let me know if you need any more information.

Thanks,
truongn2
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
First, it's not necessary to select cells in order to work with them. You can work with ranges directly in VBA. You also don't need a loop to find the first empty cell.

Instead of all that, perhaps something like this:


Code:
Dim x As Long, i As String
Worksheets("Sheet1").Select
i = cbxinitials.Text
x = Application.WorksheetFunction.CountIf(Range("A:A"), i & "*")
With Range("A" & Rows.Count).End(xlUp)
    .Offset(1) = i & Chr(65 + x)
    .Offset(1, 1) = txtFullName.Text
End With
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top