Code not working as it should

[ipbr21054]

I have in place the code shown below

In TextBox2 i enter a vin number of 17 characters.
The 10th character represents the year,so if its a 3 then when the userform values are sent to my worksheet the cell in column I for this customer should show 2003

5 would show 2005
6 would show 2006
7 would show 2007
etc etc but i see some errors.

I see the following
Vin number Cell I
9 2010 Should be 2009
3 2004 should be 2003
6 2007 Should be 2006
3 2004 Should be 2003
8 2009 Should be 2008

There are also a few more like this BUT what i dont understand is that there are also vin numbers in use which also show 3,4,5 etc etc & in cell I is the correct year is shown ???
So how come some show,
9 2010 where others show 9 2009 ??????????????

Private Sub CommandButton1_Click()
      If OptionButton1.Value = True And OptionButton7.Value = False And OptionButton8.Value = False _
      And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
     
        MsgBox "You Must Select A Lead Type", vbCritical, "Lead Type Selection Error Message"
 Else
  If Len(Me.TextBox2.Value) = 17 Then
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
    Next i
    
    With ThisWorkbook.Worksheets("MC LIST")
      .Range("A8").EntireRow.Insert Shift:=xlDown
      .Range("A8:K8").Borders.Weight = xlThin
      .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = -16776961
      
      If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
      If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
      If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
      If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
      If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
      If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
      If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
      If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"

      ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                 "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
      For i = 0 To UBound(ns)
        If Mid(Range("B8").Value, 10, 1) = ns(i) Then
          Range("I8").Value = "" & 2000 + i
          Exit For
        End If
      Next

      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
      .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
      Application.Goto Selection, True
    End With
    ActiveWorkbook.Save
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload McListForm
  Else
    MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MC LIST TRANSFER"
    TextBox2.SetFocus
  End If
  End If
End Sub

 

[mole999]

just for fun, next to the entry can you =mid(cell,9,1) on the sheet and check you are getting the value expected. If its wrong, then you code is working well, and maybe some have a leading space

 

[ipbr21054]

Sorry im lost as to where i need to put it

 

[mole999]

if your 17 character was A1 then and cell =MID(A1,9,1), its only for checking for the moment

 

[ipbr21054]

Im still lost as to where it goes
Vin numbers are in column B

Info is added to userform then i send to worksheet.
A new row is created & then i see the values appear.
I think you are saying put the code in the B cell but how can i do that it the row isnt there yet

 

[mole999]

the cell for the test ideally is on the same row as the VIN number, so =MID(B1,9,1) entered in any column that will not be used, as each of the examples appear to be up by one I suspect that some of them have might be short numbers there by moving the reference point, do any VIN have punctuation dots, commas etc. Just guessing here

 

[ipbr21054]

Hi,
All vin have 17 characters as there is a code in place not to let the value be sent to worksheet from userform if 17 isnt correct
All are letters / number
I put your code in a unused cell but i see nothing shown in it

Im going round in circle so here is a link for the file.
I have removed some data as names etc.

You will see the 10th shown in Red & to the right the year.
At the top click open form to enter the data,i used the same vin for one as a test lets say "6" and when transfered to worksheet you can see it appears as not 2006 but 2007


Excel file

 

[mole999]

sorry I can only get a blank page as a view from the upload, so even putting in a can't see where a VIN would be put in,

 

[ipbr21054]

I see what yo0u mean and google drive has done something.
I will try another way and post back in two minutes

 

[ipbr21054]

Try this please

EXCEL MC

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com