Code not working as it should

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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 ??????????????


Rich (BB code):
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Sorry im lost as to where i need to put it
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if your 17 character was A1 then and cell =MID(A1,9,1), its only for checking for the moment
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
I see what yo0u mean and google drive has done something.
I will try another way and post back in two minutes
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Try this please

 

Watch MrExcel Video

Forum statistics

Threads
1,118,983
Messages
5,575,371
Members
412,657
Latest member
georgean
Top