Results 1 to 7 of 7

Thread: Code to change multiple numbers at once
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Code to change multiple numbers at once

    Afternoon.

    In a worksheet at column B is a list of customers names.
    When a customer makes a return purchase their name is then followed by a number.
    Example,

    Tom Jones
    Tom Jones 1
    Tom Jones 2
    Tom Jones 3

    At first it was fine but now introducing a combobox list once the numbers are greater than 9 the list becomes out of order & looks a mess..

    I would like a code on a command button etc that would change the number as the list is now nearly 1000 entries.
    Example
    Tom Jones No change
    Tom Jones 1 Would then become Tom Jones 001
    Tom Jones 2 Would then become Tom Jones 002
    Tom Jones 3 Would then become Tom Jones 003

    Many thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code to change multiple numbers at once

    Code:
    With Workbooks(REF).Sheets(REF)
    
    LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    For each c In .Range("B2:B" & LRow)
        If IsNumeric(Right(c,2) And Not IsNumeric(Mid(c,Len(c)-2,1) Then
            c.Value = Left(c, Len(c)-2) & "0" & Right(c,2)
        ElseIf IsNumeric(Right(c,1) Then
            c.Value = Left(c, Len(c)-1) & "00" & Right(c,1)
        End If
    Next c
    
    End With
    Last edited by Tim_Excel_; Mar 13th, 2019 at 09:53 AM.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to change multiple numbers at once

    Hi,
    I get a syntax error and below is shown

    Code:
    If IsNumeric(Right(c,2) And Not IsNumeric(Mid(c,Len(c)-2,1) Then
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,903
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Code to change multiple numbers at once

    another one for you
    test on a copy of your worksheet!

    Code:
    Sub FormatNos()
        Dim Ws As Worksheet, Rng As Range, Cel As Range, Txt As String, S As Integer, L As Integer, Nbr As Integer
        Set Ws = ActiveSheet
        Set Rng = Ws.Range("B2", Ws.Range("B" & Rows.Count).End(xlUp))
    
        For Each Cel In Rng
            Txt = Cel.Value: L = Len(Txt): S = InStrRev(Txt, " "): Nbr = 0
            On Error Resume Next:  Nbr = CInt(Right(Txt, L - S)): On Error GoTo 0
            If Nbr > 0 Then Txt = Left(Txt, S) & Format(Nbr, "000")
            Cel = Txt
        Next
    End Sub

  5. #5
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code to change multiple numbers at once

    My bad, forgot two paranthesis:

    should be
    Code:
    If IsNumeric(Right(c, 2)) And Not IsNumeric(Mid(c, Len(c) - 2, 1)) Then
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to change multiple numbers at once

    Hi,
    I run the code but get a subscript out of range on this line of code.
    Code:
    With Workbooks(REF).Sheets(REF)
    So my workbook is called DR & the worksheet in question is called POSTAGE
    I then change the code to,
    Code:
    With Workbooks(DR).Sheets(POSTAGE)
    But still the same message ?
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #7
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code to change multiple numbers at once

    I always use REF in my code to indicate a reference to be added/changed to suit OP's code.

    You're on the right path, but not quite there. There are three ways of referencing wokbooks/-sheets:

    1. The name as a string. Either directly (Workbooks("name")), or indirectly with a variable:
    Code:
    wbname = "name"
    With Workbooks(wbname)
    '[...]
    2. The index as an integer. Refers to the order in which all workbooks were opened. (1) is the first wb, (2) is the second. E.g. Workbooks(1). Variables can also be used:
    Code:
    wbindex = 3
    With Workbooks(wbindex)
    '[...]
    3. The CodeName of a sheet. These are listed in the VB editor (Alt + F11). E.g. Sheet2



    For more information read this webpage tutorial
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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