Pull Characters From Alpanumerics and Right Align

I am trying to create a data sheet to simplify the filling of corporate documents on our company computer systems. I have a working system, flawed, but working. What I want is to simplify it and correct some mistakes I made when I first made it. At the moment the priority is the field for our serial numbers. They vary from 1 to 10 digits in length with a alpha-numeric system. Some serial numbers even include hyphens, but the hyphens need to be removed for the forms. My system uses a modified form to allow for hyphens, but my boss wants them removed. ^^'

I must apologize now, though. My boss knows I'm requesting help here and all, but he said I couldn't put the document up, sorry.

Anyways, the serial number of an item is entered into the data sheet field and my macro breaks down the serial number piece by piece. On the form the there are ten blocks for each digit of the serial number and I have coded the macro to put one digit per box. The trick is that the serial number must be right aligned and any empty fields must stay empty. This is where my request comes into play. I have already programmed the macro to do everything required except remove hyphens. The problem is that it is a REALLY long macro using If Then End statements to to compensate for the varying lengths of a serial number. Is there anyway to simplify this task?

Public Sub SerialNo()
    Sheets("Data").Select
    
    Dim SerialNo As String
    Dim SerialNo1 As String
    Dim SerialNo2 As String
    Dim SerialNo3 As String
    Dim SerialNo4 As String
    Dim SerialNo5 As String
    Dim SerialNo6 As String
    Dim SerialNo7 As String
    Dim SerialNo8 As String
    Dim SerialNo9 As String
    Dim SerialNo10 As String
    
    SerialNo = Range("AA4")
    SerialNo1 = Right(SerialNo, 1)
    SerialNo2 = Left(Right(SerialNo, 2), 1)
    SerialNo3 = Left(Right(SerialNo, 3), 1)
    SerialNo4 = Left(Right(SerialNo, 4), 1)
    SerialNo5 = Left(Right(SerialNo, 5), 1)
    SerialNo6 = Left(Right(SerialNo, 6), 1)
    SerialNo7 = Left(Right(SerialNo, 7), 1)
    SerialNo8 = Left(Right(SerialNo, 8), 1)
    SerialNo9 = Left(Right(SerialNo, 9), 1)
    SerialNo10 = Left(SerialNo, 1)
    
    Sheets("ERO").Select
    
    If Len(SerialNo) = 1 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = ""
        Range("DK4").FormulaR1C1 = ""
        Range("DE4").FormulaR1C1 = ""
        Range("CY4").FormulaR1C1 = ""
        Range("CS4").FormulaR1C1 = ""
        Range("CM4").FormulaR1C1 = ""
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 2 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = ""
        Range("DE4").FormulaR1C1 = ""
        Range("CY4").FormulaR1C1 = ""
        Range("CS4").FormulaR1C1 = ""
        Range("CM4").FormulaR1C1 = ""
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 3 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = ""
        Range("CY4").FormulaR1C1 = ""
        Range("CS4").FormulaR1C1 = ""
        Range("CM4").FormulaR1C1 = ""
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 4 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = ""
        Range("CS4").FormulaR1C1 = ""
        Range("CM4").FormulaR1C1 = ""
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 5 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = SerialNo5
        Range("CS4").FormulaR1C1 = ""
        Range("CM4").FormulaR1C1 = ""
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 6 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = SerialNo5
        Range("CS4").FormulaR1C1 = SerialNo6
        Range("CM4").FormulaR1C1 = ""
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 7 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = SerialNo5
        Range("CS4").FormulaR1C1 = SerialNo6
        Range("CM4").FormulaR1C1 = SerialNo7
        Range("CG4").FormulaR1C1 = ""
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 8 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = SerialNo5
        Range("CS4").FormulaR1C1 = SerialNo6
        Range("CM4").FormulaR1C1 = SerialNo7
        Range("CG4").FormulaR1C1 = SerialNo8
        Range("CA4").FormulaR1C1 = ""
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 9 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = SerialNo5
        Range("CS4").FormulaR1C1 = SerialNo6
        Range("CM4").FormulaR1C1 = SerialNo7
        Range("CG4").FormulaR1C1 = SerialNo8
        Range("CA4").FormulaR1C1 = SerialNo9
        Range("BU4").FormulaR1C1 = ""
    End If
    If Len(SerialNo) = 10 Then
        Range("DW4").FormulaR1C1 = SerialNo1
        Range("DQ4").FormulaR1C1 = SerialNo2
        Range("DK4").FormulaR1C1 = SerialNo3
        Range("DE4").FormulaR1C1 = SerialNo4
        Range("CY4").FormulaR1C1 = SerialNo5
        Range("CS4").FormulaR1C1 = SerialNo6
        Range("CM4").FormulaR1C1 = SerialNo7
        Range("CG4").FormulaR1C1 = SerialNo8
        Range("CA4").FormulaR1C1 = SerialNo9
        Range("BU4").FormulaR1C1 = SerialNo10
    End If
    Range("A1").Select
    
    Sheets("Data").Select
End Sub

Oh, I almost forgot, due to the rest of the form the fields the serial number is being put into are merged cells, I hope that doesn't make a difference.

Thank you for simply taking a look at my request!

Re: Pull Numbers From Alpanumerics

I don't think I was as clear as I had thought. ^^'

Attached is a blank copy of what I have, nothing of the form except where the serial number will be going. It is in XLS format for PCs at my work without 2007 on them.

Basically, the user will enter the serial number into a single cell on the data page. Some serial numbers will be 10 digits long not including hyphens. The ERO page, which is the blank form page, is set-up for only 10 digits in the serial number area. I would shorten it but the form will print onto a pre-made form that is manufactured with only 10 slots. As a result, for the 10 digit long serial numbers, or rather any length, the hyphens must be removed. In addition it must be right justified with the remaining field left empty.

I've created the really long code from my original post to split up the serial number and insert it into the blocks right justified, but it doesn't compensate for hyphens, and if possible I'd like to shorten it.

mikerickson
Thank you for the code snippet, but it didn't work.

Thanks again for simply looking at it. I really appreciate this.

Re: Pull Numbers From Alpanumerics

Remove the hyphens from serial numbers, split the result into individual alphanumeric digits, and paste those digits in the original order while right justified on the ERO page.

Also, I don't wish to sound rude of anything, but the serial number on the Data page was a demo serial I left there for use by programmers while experimenting with code. The ERO sheet was the exact form we use with everything EXCEPT the serial number location erased. My boss wouldn't let me post it otherwise. Attached to the document is also the macro that mikerickson posted for use.

Re: Pull Numbers From Alpanumerics

Thank you, the code worked perfectly to remove the hyphen, but it didn't do anything for placing the serial number into the ERO sheet.

BU4 = T
CA4 = H
CG4 = M
CM4 = 5
CS4 = 9
CY4 = 2
DE4 = 8
DK4 = 5
DQ4 = 7
DW4 = 3

The problem isn't that I can't split it or right align it, but that the code is much to extensive, it is too difficult to edit should I have to edit it.

For instance, if the serial number were GS5F-332 it would have to appear as:

BU4 =
CA4 =
CG4 =
CM4 = G
CS4 = S
CY4 = 5
DE4 = F
DK4 = 3
DQ4 = 3
DW4 = 2

The issue is with splitting up the serial number into the individual blocks.

When I defined SerialNo1 as the first letter to goto BU4 in the ERO sheet it will grab the first digit and left align it:

BU4 = G
CA4 = S
CG4 = 5
CM4 = F
CS4 = 3
CY4 = 3
DE4 = 2
DK4 =
DQ4 =
DW4 =

If I work backwards, which is what I had originally done, it defines 10 strings for 10 cells and makes the last digit in the serial number the first serial number digit to be entered into the right block, moving left. This resulted in the last digit being copied, the first digit of the serial number, to repeat in every remaining block:

BU4 = G
CA4 = G
CG4 = G
CM4 = G
CS4 = S
CY4 = 5
DE4 = F
DK4 = 3
DQ4 = 3
DW4 = 2

Is there a way to cut down the original code I put in my first post to make it appear like:

Data Page

AA4 = GS5F-332

ERO Page

BU4 =
CA4 =
CG4 =
CM4 = G
CS4 = S
CY4 = 5
DE4 = F
DK4 = 3
DQ4 = 3
DW4 = 2

That was the main thing I wished to solve, was the extensive amount of editing required to change a variable, whether to add a digit, remove a digit, or simply change how it shows up. I'm sorry for making it seem so...weird. Please, feel free to charge an extra question or two for the trouble. I got extra anticipating problems from my end. ^^'

norie
Well, my boss is technically the president of the United States. The US Military operates like that. ^^'

Anyways, to answer your question, the actual form is classified, which is why I was required to remove everything from it. I'm sorry for the trouble it's caused, but I have no option in the matter.

Also, for your question about extracting the serial number, I'm attempting to create a data sheet that will completely fill in the document as it is suppose to be filled in. On the actual document there are certain things that must be broken up by blocks, says someone 50 years ago when the form was first created. This is meant as a way for personnel that are not familar with filling out these forms to do so easily by simply providing the required information to one page and having everything else done for them.

Lastly, the merged cells is also a problem I've had no choice but to deal with. The document was designed to print onto premade forms. On these forms the blocks overlap, requiring in an Excel document to use merged cells.

Again, I must apologize for all the trouble this has caused, and any further assistance is greatly appreciated.

Re: Pull Characters From Alpanumerics and Right Align

Thank you for the codes, they all worked perfectly for shortening the code, but they are still left aligned. I'm sorry about all this, but I'm not good with VBA. ^^'

Re: Pull Characters From Alpanumerics and Right Align

I keep getting a syntax error on the line:

.Value = Mid(strSer, lChar, 1)

Thank you for trying.

Re: Pull Characters From Alpanumerics and Right Align

For this I'm uploading a fairly complete copy of the form. I removed certain fields due to...issues.

Anyways, with the exception of the fields I had to remove that is the actual document I've been working on with ALL the code and the different versions of them that I've used. I did edit the code provided to use a cell for dynamic serial numbers instead of the static text of the original codes provided.

Other than that, I don't recall editing it, and due to my current situation I can't compare the code from here to that of the document. ^^'

With the current code set as SerialNo I get a syntax error.

Re: Pull Characters From Alpanumerics and Right Align

I can't believe I didn't get this before! You guys are all amazing, I just didn't put things together.

Although none of the code snippets did EXACTLY what I wanted, every part I needed was in a version of the code!

After have brooded over it for a few days I figured it out, thanks heavily to everyone for helping.

Public Sub SerialNo()
    Dim SerialNo As String
    Dim lChar As Long
    Dim strAddress As String
    
    SerialNo = StrReverse(Replace(Sheets("Data").Range("AA4"), "-", ""))
    
    With Sheets("ERO")
        For lChar = 1 To Len(SerialNo)
            strAddress = Choose(lChar, "DW4", "DQ4", "DK4", "DE4", "CY4", "CS4", "CM4", "CG4", "CA4", "BU4")
            With .Range(strAddress)
                .Value = Mid(SerialNo, lChar, 1)
            End With
        Next lChar
    End With
End Sub

You guys are absolutely amazing and I'm sorry for all the trouble I caused. ^^'

I must ask one more thing though, could someone please remove that attachment from my last post, I'm afraid I don't know how.