VB Code

[Walter Haenn]

I need help with solving the following problem. I have develop a spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used car) I
need to write a code or use a function that allowS me to take the model year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model year car.
For those years prior to 2000 letters are used to indicate model year (X=
1999).
Any help or suggestions is appreciated.

Walter

 

[Rick Rothstein \(MVP - VB\)]

Something like this maybe?

=IF(MID(A6,10,1)="X","1999 or earlier",2000+MID(A6,10,1))

I wasn't sure of how the letters relate to the years prior to 2000 (sorry, I
am not familiar with VIN numbers, so all I have to go on is what you post),
so I just lumped them together as "1999 or earlier". If you can provide more
detail about this, we can adjust the formula accordingly.

Rick

 

[Walter Haenn]

Rick,
Using MID(A6,10,1) will indentify the 10 digit. I now need to take that
10th digit and translate it to a year. i.e. 4 = 2004, 1 =2001, X= 1999, etc.

 

[Rick Rothstein \(MVP - VB\)]

You didn't read my first post carefully enough. The formula I posted will
give you the year for a 10 character that is a number (try it); however, I
said I don't know how VIN numbers work, so I don't know what letters are
possible for the 10th characters besides the "X" you mentioned. Is that the
only possible letter there can be? If so, the formula I posted will work.
However, if there can be other letters, you need to tells us what they are
and what years they represent. I might have guessed that "W" was 1998 and
"V" was 1997, but that would mean VIN number system was created in something
like 1977 which seemed unlikely to me.

Rick

 

[Walter Haenn]

Rick,
I appreciate your help. I am not sure that I am explaning myself correctly.
I tested the function MID in my current worksheet, and it gave me the
correct digit for the 10th position in the VIN. I now need to take the that
digit anKd translate into a year. The reason is that the VIN and the
modelyear in the VIN will change with every entry. We currently are using
model year 1996 to 2007 in our program. While you were answewring this
posted, I was testing the LOOKUP function, but have run into a snag. The
formula will not accept and entry from another cell that is dependent on
another cell.
Here is what I am using... =LOOKUP(O3,K6:K16,L6:L16) where O3 has the
result of the MID function you help me with. K6:K16 is the range of digits
from the MID range ( X to 7) L6:L16 are the Model Years that correspond.

When I try this I get "#N/A"

Thank you for your help.

Walter

 

[Walter Haenn]

Ok, Rick I guess this is why I need your help. I am a little thick. I try
the formula as you strongly suggested, and it works. However, I need to
modify it for model years 1999 and earlier. Thoughts?

Thank you

Walter Haenn

 

[Rick Rothstein \(MVP - VB\)]

I'll try once more... I don't know the encoding used in VIN numbers. All you
told me about years 1999 and earlier are the letter "X". What letters apply
to what years prior to that? For example, what would the 10th character be
for the year 1998? 1995? 1980? 1977? 1975? 1970? 1960? I'm looking for the
(mathematical) pattern behind the entries in that 10th character position.
Also, it just occurred to me... what will be put in the 10th character
position for the years 2010, 2015, etc. (when we run out of single digits)?

Rick

 

[Rick Rothstein \(MVP - VB\)]

Ignore my other question... I looked VIN numbers up on line. What a mess
that system is. Anyway, for the model years 1981 through 2009 (I have no
idea what they will do when they run out of digits), this formula should
work...

=1980+FIND(MID(A1,10,1),"BCDEFGHJKLMNPRSTVWXY123456789")

Rick

 

[Walter Haenn]

Rick, Thank you for your help. The industry will no doubt revert to the
Letter system that was in use before 2000.

 

[Rick Rothstein \(MVP - VB\)]

Rick, Thank you for your help.

You are welcome. I am assuming the formula does what you want and covers the
years you are interested in (I couldn't find very much about the structure
of VIN numbers prior to 1981).

The industry will no doubt revert to the
Letter system that was in use before 2000.

As I said, what a mess that system is... hell, they didn't even start with
the letter "A" back when they first started using letters (so, assuming you
are right about the letters, who knows what letter they would use for 2010
then).<g>

Rick