Formula to auto fill a name
Results 1 to 4 of 4

Thread: Formula to auto fill a name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular UMAKEMESIK's Avatar
    Join Date
    Oct 2005
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to auto fill a name

    I am reposting an old post in which I tried to use but does not work

    am I doing something wrong.

    below is the original post:

    -----------------------
    Here is a Vba solution:

    If you will be entering all your values in column "A" for example use this:

    Put a list of your shortened values like "Ja" for January into column "E" starting in row(1) and as far down as you like.

    And in column "F" adjacent to "Ja" put the full value like "January"

    Now any time you enter "Ja" in column "A" the value will be changed to "January"

    You can modify this script to include columns A to C or what ever you want. And you can modify the columns with your shortened and full text values if you want.

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Application.EnableEvents = False
    Dim c As Range
        For Each c In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
            If c.Value = Target.Value Then Target.Value = c.Offset(0, 1).Value
        Next
    End If
    Application.EnableEvents = True
    End Sub
    on my wb in inserted a module1
    and put this in there.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Application.EnableEvents = False
    Dim c As Range
        For Each c In Range("P1:P" & Cells(Rows.Count, "P").End(xlUp).Row)
            If c.Value = Target.Value Then Target.Value = c.Offset(0, 1).Value
        Next
    End If
    Application.EnableEvents = True
    End Sub

    you will see on my frst sheet the rage is column b for the entry
    and on my sheet I enter items in B2

    I put the abbreviated list in p starting In p2
    and the full name list in Q starting in Q2

    and nothing happens.

    anyhelp would be appreciated.

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to auto fill a name

    Hi,

    What you have is a "Worksheet Change" event code, you need to insert the code in the "Worksheet" module in the sheet where you're entering data, Not a standard module as you described.

    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window
    Last edited by jtakw; Jun 7th, 2019 at 07:05 PM.

  3. #3
    Board Regular UMAKEMESIK's Avatar
    Join Date
    Oct 2005
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to auto fill a name

    Quote Originally Posted by jtakw View Post
    Hi,

    What you have is a "Worksheet Change" event code, you need to insert the code in the "Worksheet" module in the sheet where you're entering data, Not a standard module as you described.


    That was so simple to fix, thanks for pointing me in the right directions.

    all works great now.

    Have a good one.

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to auto fill a name

    You're welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •