+ Reply to Thread
Results 1 to 10 of 10

Change last letter in active cell to next incremental letter

  1. #1
    Registered User
    Join Date
    08-23-2019
    Location
    VA
    MS-Off Ver
    Office 365
    Posts
    5

    Change last letter in active cell to next incremental letter

    I'm trying to figure out how/if I can change the last letter in a selected cell to the next incremental letter using VBA. For instance, the current selected cell is "19054Aa" and I want to change it to "19054Ab".

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Change last letter in active cell to next incremental letter

    Using formula
    ="19054A" & Char(96 + Row(A1))

    Copy down.
    Same logic applies to VBA.

    But you'd need to supply bit more info. What should happen when you reach "z"?

    I'd recommend uploading sample workbook. To upload use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Mumbai, IN
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Change last letter in active cell to next incremental letter

    Here's how you do it

    Please Login or Register  to view this content.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Change last letter in active cell to next incremental letter

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Registered User
    Join Date
    08-23-2019
    Location
    VA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Change last letter in active cell to next incremental letter

    I'm new on this forum. Hopefully I attached the sample file correctly.

    I created a spreadsheet with all my projects and their bid status. I often get requests for updates to the bids. So, I add it to the bottom of my table and change the end of the job #. It's not how I would prefer to label my job numbers, but I inherited it.

    I added the "Update" button in the top right corner that copies the active row and pastes it at the bottom of the table and then changes a couple other values. I'm sure this isn't the most efficient way. But, my VBA skills are pretty limited. Here's what I currently have:
    Please Login or Register  to view this content.
    I can't just set the value as "19054Aa" because I need to change with whatever the current job number is. So, if I needed to make an update to "19057Aa" in the future, I would need it to make it go to "19057Ab".

    I also tried adding in yoursamrit2000's code but it didn't change anything.

    Also, I'm not concerned about what happens after Z. I've never gotten that far. If I ever reach revision z+1, I think I may throw in the towel and tell them they can find another contractor.

    Thanks
    Attached Files Attached Files
    Last edited by dwetzel01; 08-23-2019 at 12:40 PM.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Change last letter in active cell to next incremental letter

    Something like below.
    Please Login or Register  to view this content.
    Last edited by CK76; 08-23-2019 at 12:02 PM. Reason: xlPasteValues changed to xlPasteAll

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Change last letter in active cell to next incremental letter

    Oh, FYI if you want to retain formula in "Elapsed" column. First copy down to end of your current table.

    Code should be amended to use .FillDown instead of copy/paste.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Change last letter in active cell to next incremental letter

    Please take a moment to read our forum rules...here
    Your post #5 does not comply with Rule # 2
    2. Programming code must be enclosed in code tags to improve readability. (A, Z)


    Please Login or Register  to view this content.
    So...Edit your post...Highlight the code and press the # button

  9. #9
    Registered User
    Join Date
    08-23-2019
    Location
    VA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Change last letter in active cell to next incremental letter

    CK76: I like this. The problem I run into with that is, it pulls from the data in the cell directly above it. So, if I need to create an update that isn't the last entry, it updates it with the last job # entered + the changed last letter. So, if you look at the spreadsheet I attached earlier, I may get a request later to update the job entered in Row 10 but I've already added 4 entries since then. That code adds everything fine, except it pulled the Job # (along with all the other info) from Row 14 and changed it to 19054Ac but should be 19052Ac.

    sintek: I apologize for the incorrect message format. I've made the correction and read the rules. Thanks for letting me know.
    Last edited by dwetzel01; 08-23-2019 at 01:28 PM.

  10. #10
    Registered User
    Join Date
    08-23-2019
    Location
    VA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Change last letter in active cell to next incremental letter

    I ended up getting it to work by including Sintek's earlier suggestion w/ 2 modifications. First, I changed "Val" to copy the value in B instead of making it a fixed value. The other problem I ran into was that "NewVal" was correct but it wasn't actually pasting it into the cell. So, I just added ActiveCell.Value = NewVal and that seemed to do the job.
    Please Login or Register  to view this content.
    Again, I know this is probably pretty messy but it retains all the correct data from the row I need to copy, not necessarily the one right above it. Thanks everyone for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 25
    Last Post: 04-21-2015, 10:19 AM
  2. Format:change headers from letter to number/number to letter
    By Biff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  3. Replies: 2
    Last Post: 07-26-2005, 03:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1