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
```

Display More

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!