How to add digit or replace last digit in string based on criteria

< Back to Search results

 Category: [Excel]  Demo Available 

How to add digit or replace last digit in string based on criteria




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:






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.


stars (0 Reviews)