Tracker
HOME    TRACKER    Excel

# Vehicle Identification Number Function

## I need a function to return a boolean T/F to check if a 17 character string meets the following checksum validation. This is for Vehicle Identification Numbers for vehicle made after 1981 I need a VBA function that I can call multiple times in a workbook, Example =VIN(A1) which returns True or False. First, find the numerical value associated with each letter in the VIN. (I, O and Q are not allowed.) Digits use their own values. A->1B->2C->3D->4E->5F->6G->7H->8 J->1K->2L->3M->4N->5P->7R->9 S->2T->3U->4V->5W->6X->7Y->8Z->9 Second, look up the weight factor for each position in the VIN except the 9th (the position of the check digit). 1st->82nd->73rd->64th->55th->46th->3 7th->28th->1010th->911th->812th->713th->6 14th->515th->416th->317th->2 Third, multiply the numbers and the numerical values of the letters by their assigned weight factor, and sum the resulting products. Divide the sum of the products by 11. The remainder is the calculated check digit. If the remainder is 10, the calculated check digit is the letter X. Finally, if the calculated check digit did match the 9th digit of the VIN entered by a user, the VIN passed the checksum test. It failed the checksum test otherwise.

Related Forum Messages:
Vlookup: Function That Will Search For The Value Of The Vehicle
I have a lookup table (see attached file) which holds the min & max value for a vehicle, the min & max size of the engine and the premium. I want a function that will search for the value of the vehicle (e.g. 100000 in cell H5) within the min value and max value columns as well as the engine size (e.g. 2000 in cell H6) in the min size & max size columns return the premium from the associated premium (which should be 13,200).

Vehicle Age
Basically, i want to work out the age of a large list of vehicles based on its uk registration plate number but have so far been completely out of luck,

One To One, And One To Many Identification
I have a spreadsheet that loads a huge amount of data (50,000 rows) that needs to be parsed as follows:

Below are the Broker Names listed in Column A:

Column A:
Broker Name 1 = Tom
Broker Name 2 = Dick
Broker Name 3 = Harry
Broker Name 4 = Frank

Below are the Broker Codes listed on another section of the same worksheet:

Column R (for example):
Broker Code for Broker Name 1 (Tom) = BT
Broker Code for Broker Name 2 (Dick) = BD
Broker Code for Broker Name 3 (Harry) = BH
Broker Code for Broker Name 4 (Frank) = BT

I would like to have the Broker codes (e.g. BT, BD, BH or BT) put in Column C next to the corresponding Broker names. The part that I am having difficulty with is that it is a mixed situation whereby only one Broker Code (e.g. BD and BH) applies to one specific Broker Name, while other Broker Codes (e.g. BT) may apply to multiple Broker Names.

Can a formula be developed that could be copied and pasted in Column C that would compare the list of codes against the list of names and return the appropriate Broker Code?

Fill Vehicle Allocation Table
I am reposting this as my previous post was deleted.. i dont know the real reason for the same...

The raw data in the attachment contains:

•C1 to AI1 refers to vehicle numbers.
•Column A refers to Shift Time
•Column B refers to Number of vehicles required in that particulars Shift time

The output:

•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time

•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.

•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).

• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated

Languages Identification Aid
One Excel file with two columns that is populated with a description code and a description. The total number of lines is 30 000 and the description is on the Languages of English, Spanish and Portuguese.

I need to indicate in the third column all the descriptions that are not English.

Identification Of Special Numbers
I got some 10 digit numbers in column A, need to get the marking in column B as per the criteria given below for last 5 digit numbers. The checking should start with platinum. on getting a macro or any formulas for each criteria.

Category- For Last 5 Numbers MARKING Samples
Numbers Ending with Double Digit SILVER 9280015622
Ending with 786 (Without 0) SILVER 9280025786
First 2 Digits-0 & Last 3- From 101-998 SILVER 9280000101-998
Double Digit Repetition with 0 Separator GOLD 9280055055
Numbers in Sequence GOLD 9280012345
Numbers Ending with Triplets GOLD 9280052333
Numbers in Round Hundreds GOLD 9280082500
Numbers in Round Thousands GOLD 9280065000
Ending with Triplet GOLD 9280052333
Mirror Image Numbers GOLD 9280012321
Triplets with 0 as Prefix or Suffix GOLD 9280010444, 9280014440
Numbers having 786 (Others 0) XCLUSIVE 9280000786, 9280100786
Single digit Ending (All Others 0) XCLUSIVE 9280000001-9
Triplet followed by Double Zeros XCLUSIVE 9280055500
2 Consecutive Triplets XCLUSIVE 9280111555
Last 4 digit 0 PLATINUM 9280010000
All Penta Numbers Including 0 PLATINUM 9280011111

Add Ticks In Cells For Copy Identification
I have this excel sheet with 200+ (number can change depend of situation) checkboxes here and I would like for the worksheet to do the following:

1) when i click on the checkbox, it copies all the data in the same row as the checkbox to a seperate sheet (sheet2) and not leaving any spaces if checkboxes are not click in order.

2) when i uncheck the checkboxes, the row that associated with the checkbox will be removed.

3) the checkboxes will all be uncheck with a commandbutton (reset switch)

4) variable number of checkboxes so that i dont have to link the subroutine to every checkbox with the move and remove subroutine. (because there will be unknow number of checkboxes and mostly 200+)

example (want to be able to replace all the 1, 2, 3...with an X or any way possible)

Private Sub CheckBox1_Click()
MoveCheckBoxData CheckBox1
End Sub

Private Sub CheckBox2_Click()
MoveCheckBoxData CheckBox2
End Sub .........................

Function 2 Divide A Number (ie \$1.00) By % (ie 25%) To = (\$4.00)?
=1/25%

is it that you want a UDF

Function MyFun(a,b)
MyFun = a / b
End Function

Usage:
=MyFun(1, 25%)

Mangesh

"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message

Function To Count The Number
I have a 2500+ line document with different years indicated in column D. How
would I use the count (??) function to count the number of occurances for
1998, 1999, 2000, etc?

IF Function For A Number Format
I need a formulat that will ensure that a cell has 12 numeric characters,a dash, and then 3 more numeric characters. I thought this was a simple formula but I cannot get anything to work.

############-###

VBA Function - Extract Number(s) In String
My knowledge of functions is pretty limited, or negligible to be honest. I require a function to extract numbers from each cell in a selected range.

Eg.

cells contain the following data:

cell 1: xxxxxx 45,59
cell 2: x xxxxx xx 6,45,27
cell 3: x xxxx 28

were x represents text

I need to loop through each cell and extract each of the numbers and list them in a separate range. If applied to the above range of cells the function would show the below in the ouput range. Each number in its own cell.

6
27
28
45
45
59

Get The Number Of Workdays Between Two Dates In A VBA Function
Get the number of workdays between two dates in a VBA function. I tried simply using the Advanced Toolpak function NETWORKDAYS, but I get the error message "Sub or function not defined". I did reference to atpvbaen.xls in the Tools > References menu, so I don't know why Excel is not recognizing the function.

I used the following function to test it:

Function CalcNetDays(dInitial As Date, dEnd As Date)
CalcNetDays = NetworkDays(dInitial, dEnd)
End Function

Any reason the function is not working?
Also, I need to add the holidays to the function. The holidays are listed on a worksheet column. Do I simply add the range to the function, like below?

CalcNetDays = NetworkDays(dInitial, dEnd, Range("Holidays"))

Changing Colour Of Number Using IF Function
Is it possible in anyway to make a formula that changes the colour and boldness of a number depending on whether it is bigger than 50 or 100?

eg. if i enter all my numbers as dark grey. then i enter say, 65, i want it to automatically change to black and bold. if i enter 105, i want it to automatically change to red and bold without me having to do it manually?

Using VLOOKUP Return As Row Number In Function
I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date. I now need to use that returned value in the CORREL function. That is, I would like it to look something like:

When I enter this, I am told that I have an error. Is there a better way to nest this vlookup?

Function To Count Number Of Text Instances
This might be really simple but i don't get it. I have a column with country names (strings). There would be 5 instances of "USA", 10 of "UK", etc, etc.

I made a column next to it, where i want to count the number occurances ....

Hard Coded Number Range Into A Function
All I need is to use the forecast function =FORECAST(I902,F832:F901,I832:I901) but want to substitute a hard coded from and to value list, such as something like =forecast(71,F832:f901,{1-70})

That is, forecast case 71 given a range of 1 through 70. What is the correct syntax to give a from and to value? Curly brackets don't seem to work, and I don't want to do a list like {1,2,3,4,5,6,7...} which would be a pain.

Using 'left' Function To Take Out Number Upto A Certain Character
Pretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end.

eg. 1000x I need to get out the 1000
or 2p I need the 2

I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!

Function That Inputs A Certain Number Of A Specific Character
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.

We have example P/N:
1234567890123
12345678
123456

should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"

Is there a function that inputs a cerain number of a specific character ("space" in this example)?

Find Column Number And Then Use Index Function
I have a database with over 100 products listed across the first row.
Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.
For Example

A B C D E etc.
Products --> X Y Z AA
Proj 1 2 3
Proj 2 1 4 5
Proj 3 2 4
etc.

I want to be able to create a report for any given product.
The report could look like,
Product Z
Proj 1 3
Proj 3 2

So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.

I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.

Force LEFT Function To Recognize A Number?
Basically I have a phone number in column A:

123-456-1234

In column B, I want it to show me the first 3 numbers from the left, (so 123)

So I do =LEFT(A2,3)

Which gives me 123, but it's displayed as text, which ruins my whole formula that looks up the area code and displays the state.

I googled the problem and found http://exceltip.com/st/Make_LEFT_Fun...umber/778.html

which tells me to do:
=IF(LEFT(A1,1)=1,"Ignore",A1) [sees 1 as a number]
=IF(LEFT(A1,1)+0=1,"Ignore",A1) [sees 1 as a number]
=IF(LEFT(A1,1)="1","Ignore",A1) [sees 1 as text]

but when i try that it just displays the ENTIRE phone number: 123-456-1234

Mid Function :: Match Account Number With Description
I am trying to use a Mid function to help me in my vba problem.

In my first table I have a list of account numbers. E.g. 'ZZ500543'

In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'

I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?

Give Cell Number After Letter With Function.
I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

Match Function For Cell Reference Row Number
I am trying to acccess a cell value from a seperate worksheet. The cell reference needs to be generated by a seperate formula. In this example

=DSInfo!C28 // provides the correct result i.e the contents of cell C28 on worsheet DSInfo =MATCH(A4,DSInfo!C1:C35,0) //provides the correct row number - in this case 28

However on trying to combine the two =DSInfo!C&MATCH(A4,DSInfo!C1:C35,0) // provides only a formula error. I'm guessing this is a simple syntax error on my part but after hours of trying various ways I'm still having no luck.

Force Text Function To Return Number
I am using a MID fn to extract a single digit from a numeric string. I then use an IF Function to return a value based on the extracted digit. My IF statement works fine on manually entered digits but doesn't recognize my MID fn result. What am I missing?

Limit On Number Of Random Numbers In Prob() Function
I'm using the prob() function, and it works for no more than 61 random numbers. Does anyone know of a limit on the number of random numbers used in the function, and whether it's possible to use more numbers in the function? My formula is:

=PROB(\$C\$11:\$C\$71,\$L\$11:\$L\$71,G11,H11)

SUM Function - Return The Sum Of Quantities Ordered For Stores With ID Number Between 100 And 199
I have a table with client ID in column A (range A2:A200) and qty ordered by those clients listed in column B (range B2:B200). Clients’ ID numbers range from 101 to 999.

Except through filtering, how to return the sum of quantities ordered for stores with ID number between 100 and 199 ONLY?

Calculate The Number Of Days Between Two Dates Using The Days360 Function
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.

VLOOKUP Function: FIND The Number AND WRITE 'YES" TO OTHER COLUMN
ColumnA Column B ColumnC (Yes/No)
2121 345766
23423 6456546
4234 6456456
7567 64566456
76756 654645
67567 3344534
76575 34534534
756756 45345
7657 534545
756767 4534534
756756 435345

Hello,
What I am trying to do is

Search the number written at ColumnB in ColumnA and if found write YES to column C

if not write No

Lottery Number Generation: Create A Single Line Function
You must install "Analysis TookPak-VBA" before doing this; check in your Excel's Tools/Add-Ins menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes.

=RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)

Warning Message :: When A Particular Cell Function Result Is A Specific Number
I want to have a warning message appear when a particular cell function result is a specific number, probably very simple to achieve.

Randbetween Function To Only Randomate A Number When A Certin Object Is Pressed
Is it possbile with the randbetween function to only randomate a number when a certin object is pressed? As at the moment, every button i press or drop down data i select it will randomate a number, which i dont want to happen.

Index Function (isolate One Number At A Time And Evaluate Usage)
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.

I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.

Reformatting Date Into Number. Reformat Cell Not Working. Function Maybe?
I am rewriting functions for my companies inventory spreadsheets. Excel for some reason took the item codes and formatted them into date format. Right now it is in 10/01/1902 format, and it needs to be in 10-1902 format. I've tried reformatting the cells but so far I haven't found one that works.

Counts The Number Of Hours That This Person Works & Lookup Function
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:

Cell A - Name
Cell B - Monday
Cell C - Tuesday
Cell D - Wednesday
etc

Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.

The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.

Histogram Function: Find Number Of Cells That Falls Within Each Of These Max Values
i m given 12 max value of my 292 cells. now im asked to find number of cells that falls within each of these max values?? im asked t use histogram. how will i do it?

Log Function In A Macro: Take Log Of Each Number (on The Base 2) And Show The Result In The Adjacent Column
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).

Create A User Defined Function To Search A Column Of Data For A Part Number
I want to create a user defined function to search a column of data for a part number.

If it exists I want to have a the UDf returna "fail" otherwise "pass"

Here is the code I was trying to use

Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
Else
firstpass = "Fail"
End If

End Function

This function only returns a "#value" and I don't quite know how to troubleshoot it.

Dynamic Sum Function (number Of Sheets Can Vary From Time To Time)
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.

Any idea about a dynamic sum function that will do this, in combination with VBA?

Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

Nested IF Function Error (entered Too Many Arguments For This Function)
I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult

Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select

If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

User Defined Function Function Tooltip
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

2007 Right Function With Embeded Find Function
I have a range of cells, for this example I will use 2.

Cell E17 = 77/170
Cell E18 = 8/9

Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))

This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"

Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.

Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....

Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

Show Insert Function Dialog Pre Set To Function
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

Read The Number And Check For The First Number With The 2nd Number
I have this set of number..

987654.

What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.

AVERAGE Function Embedded Within An IF Function
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221