none
Excel VB code to capture first later

    Question

  • Dear All,

    Can you help me to VB code in excel for example below?

    At Column A1 is the data that we need to put and Column B, it will capture automatic the first later of Jone"J" and the rest is after space "Paul" and the result at column B1 is: JPaul

    Best regards,

           A                      B

    1   Jone  Paul         JPaul

    Note: If we delete or changed data from A, B will deleted or changed too


    BruceABC


    • Edited by BruceABC Thursday, February 8, 2018 11:08 PM
    Thursday, February 8, 2018 10:57 PM

Answers

  • Hi BruceABC,

    Here's my sample:

    [Code]
    ' --- when value of column A, B changed
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Dim tempValue As String
        Dim posSpace As Integer
        ' ---
        If Intersect(Target, Range(Cells(1, 1), Cells(lastRow, 1))) Is Nothing Then
            ' --- Column A: not changed
            Exit Sub
        Else
            ' --- Column A: changed
            tempValue = Cells(Target.Row, 1).Value  ' -- 1. get full string
            tempValue = Trim(tempValue)             ' -- 2. remove space
            posSpace = InStrRev(tempValue, " ")     ' -- 3. get position of space from right
            ' ---
            If (posSpace = 0) Then
                Cells(Target.Row, 2).Value = ""
                Exit Sub
            End If
            ' --- 4. first one letter + latter letters after space position
            tempValue = Left(tempValue, 1) _
                    & Right(tempValue, Len(tempValue) - posSpace)
            ' ---
            Cells(Target.Row, 2).Value = tempValue
        End If
    End Sub

    Bruce, don't get angry....
    I guess you are a student and it is a school assignment. Is this correct?

    Regards,

    Ashidacchi

    • Marked as answer by BruceABC Friday, February 9, 2018 7:15 PM
    Friday, February 9, 2018 12:45 AM
  • Hi Bruce,

    Do you think about provided code what it mean, what it does?
    I'm wondering what you are doing is only copy&paste without thinking code. If so, that would never bring you forward.  Please learn code, programming, because your are leaning it.

    What you want to do will be accomplished, if you think about If-sentence and modify it.
    If Intersect(Target, Range(Cells(1, 1), Cells(lastRow, 1))) Is Nothing Then
            ' --- Column A: not changed
            Exit Sub
        Else
            ' --- Column A: changed
            .....
    End If

    Regards,

    Ashidacchi

    • Marked as answer by BruceABC Saturday, February 10, 2018 3:20 AM
    Saturday, February 10, 2018 12:10 AM

All replies

  • Hi BruceABC,

    Here's my sample:

    [Code]
    ' --- when value of column A, B changed
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Dim tempValue As String
        Dim posSpace As Integer
        ' ---
        If Intersect(Target, Range(Cells(1, 1), Cells(lastRow, 1))) Is Nothing Then
            ' --- Column A: not changed
            Exit Sub
        Else
            ' --- Column A: changed
            tempValue = Cells(Target.Row, 1).Value  ' -- 1. get full string
            tempValue = Trim(tempValue)             ' -- 2. remove space
            posSpace = InStrRev(tempValue, " ")     ' -- 3. get position of space from right
            ' ---
            If (posSpace = 0) Then
                Cells(Target.Row, 2).Value = ""
                Exit Sub
            End If
            ' --- 4. first one letter + latter letters after space position
            tempValue = Left(tempValue, 1) _
                    & Right(tempValue, Len(tempValue) - posSpace)
            ' ---
            Cells(Target.Row, 2).Value = tempValue
        End If
    End Sub

    Bruce, don't get angry....
    I guess you are a student and it is a school assignment. Is this correct?

    Regards,

    Ashidacchi

    • Marked as answer by BruceABC Friday, February 9, 2018 7:15 PM
    Friday, February 9, 2018 12:45 AM
  • Dear Marked,

    I am really appropriated of time you spent on me!

    Yeah, you are right, I am learning it, i have a lot of improved and i have some assignments too.

    never get angry with you, more you asked about me, more i am so glad.

    Just additional question, In this code,This is working from A1 B2

    But if i would like to start from A15 B15, Could you let me know?

    I love Microsoft that they have best services to take care all their customers around the world, They saved the world

    May God bless you


    BruceABC


    • Edited by BruceABC Friday, February 9, 2018 8:32 PM
    Friday, February 9, 2018 7:21 PM
  • Hi Bruce,

    Do you think about provided code what it mean, what it does?
    I'm wondering what you are doing is only copy&paste without thinking code. If so, that would never bring you forward.  Please learn code, programming, because your are leaning it.

    What you want to do will be accomplished, if you think about If-sentence and modify it.
    If Intersect(Target, Range(Cells(1, 1), Cells(lastRow, 1))) Is Nothing Then
            ' --- Column A: not changed
            Exit Sub
        Else
            ' --- Column A: changed
            .....
    End If

    Regards,

    Ashidacchi

    • Marked as answer by BruceABC Saturday, February 10, 2018 3:20 AM
    Saturday, February 10, 2018 12:10 AM
  • Hello Sir,

    I am sorry, I am working and studying and working so my time too tight! I am learning lot..

    Thank you


    VeasnaYim

    Saturday, February 10, 2018 3:21 AM
  • Hello VeasnaYim,

    I'm sorry, I thought you were a student. So, I didn't provide code.
    Please modify your code like this:
    If Intersect(Target, Range("A:A")) Is Nothing Then
            ' --- Column A: not changed
            Exit Sub
        Else
            ' --- Column A: changed
            .....
    End If
    In this case, you don't have to use a variable "lastRow".
    i.e. Whenever value of column A is changed, value of column B will be changed automatically. (if value of column A include two separate words delimited by one or more than one space.)  

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Saturday, February 10, 2018 3:37 AM
    Saturday, February 10, 2018 3:36 AM