How to add digit or replace last digit in string based on criteria
Category: [Excel] Demo Available
How to add digit or replace last digit in string based on criteria
Requirement:
The user is looking for a macro to add a number "1" to a string in column A if there are currently only 8 digits in the cell. If there are nine digits in the cell, I need to replace the last one with a "1". The user has attached an example workbook with desired results - please refer to the link below:
Solution:
Code:
Sub jlsprink()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim rng As Range
For Each rng In Range("A2:A" & LastRow)
If Len(rng) = 9 Then
rng = Left(rng, Len(rng) - 1) & 1
ElseIf Len(rng) = 8 Then
rng = rng & 1
End If
Next rng
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to find and write cells based on criteria |
| How to delete rows if cell doesn't contain criteria |
| How to copy and paste chosen columns based on two criteria |
| How to count cells in a dynamic range matching two criteria given in table headers |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.