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".
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".
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
Here's how you do it
Please Login or Register to view this content.
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!!!
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:
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".Please Login or Register to view this content.
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
Last edited by dwetzel01; 08-23-2019 at 12:40 PM.
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
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.
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)
So...Edit your post...Highlight the code and press the # buttonPlease Login or Register to view this content.
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.
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.
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!Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks