1) REading the code I see that the test you asked for IS
there already!


2) why not make this a function?
Function testme(text)

Dim WMILookupTable As Range
Dim res As Variant

Application.Volatile

If Len(text) < 3 Then
testme = text & ": is not 3 letters"
Exit Function
End If

With Worksheets("wmi table")
Set WMILookupTable = _
.Range("a1:b" & .Cells(.Rows.Count, _
"A").End(xlUp).Row)
End With
On Error Resume Next
res = Application.VLookup(text, _
WMILookupTable, 2, False)
If IsError(res) Then
testme = text & " is not defined"
Else
testme = text & "=" & res
End If
On Error GoTo 0
End Function

in D1
=Testme( a1 & b1 & c1 )


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Thanks for your code Tom. I did not have a clue where to
start.

I went with another reply by Dave Peterson:
----------------
First, some housekeeping:

I used cells A1, B1, C1 to get the WMI code.
I used D1 for the message cell

I added a worksheet named "WMI Table" that consisted of
the codes in column
A
and the description in column B.

Then I dropped a button from the Forms toolbar on the
sheet with WMI input
cells.

I assigned it this macro:
Option Explicit
Sub testme()

Dim myWMI As String
Dim testWks As Worksheet
Dim WMILookupTable As Range
Dim res As Variant
Dim msgCell As Range

With Worksheets("wmi table")
Set WMILookupTable = .Range("a1:b" _
& .Cells
(.Rows.Count, "A").End(xlUp).Row)
End With

With ActiveSheet
Set msgCell = .Range("D1")
myWMI = .Range("a1").Value & .Range("b1").Value
& .Range("c1").Value

Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(myWMI)
On Error GoTo 0

If testWks Is Nothing Then
res = Application.VLookup(myWMI,
WMILookupTable, 2, False)
If IsError(res) Then
msgCell.Value = myWMI & " is not defined"
Else
msgCell.Value = myWMI & "-" & res & " is
not defined"
End If
Else
msgCell.ClearContents
Application.Goto testWks.Range("a1"),
scroll:=True
End If
End With

End Sub
--------------

I liked your idea of including a validation to ensure
only three characters
were entered using the Len function.

I want to include data validation into the sub. For
example:

If Len(myWMI) = (does not equal three) Then
display
MsgBox "Please enter only three characters"
stop procedure (eg. exit sub)

how do I do that. I want it to check the data (myWMI)
and if it does not
equal three then stop the procedure and where do I
include that in the sub?



"Tom Ogilvy" wrote in message
...
Assume list of WMI is in M1 to M50 and decoded name is
N1 to N50
on same sheet as entry is being made.

Assume entries are made in A1:C1

Private Sub CommandButton1_Click()
Dim sWMI As String
Dim wksh As Worksheet
Dim rng As Range
Dim res As Variant
sWMI = Trim(Range("A1").Value) & Trim(Range
("B1").Value) & _
Trim(Range("C1").Value)
If Len(sWMI) < 3 Then
MsgBox "Please reenter choice in A1:C1"
Exit Sub
End If
On Error Resume Next
Set wksh = Worksheets(sWMI)
On Error GoTo 0
If Not rng Is Nothing Then
Application.Goto wksh.Range("A1"), True
Else
res = Application.VLookup(sWMI, Range("M1:N50"), 2,
False)
If Not IsError(res) Then
MsgBox sWMI & " " & res & " is not on decoder"
Else
MsgBox sWMI & " is not recognized by decoder"
End If
End If
End Sub

--
Regards,
Tom Ogilvy





Nigel Cummins <ncummins-nospam@delete-this-
bit.bigpond.com wrote in
message
...
I am trying to write a workbook that will decode
Vehicle Identification
Numbers VIN. The first three characters of every VIN
has the World
Manufacturer Identifier WMI. I am creating a
worksheet specific to each
manufacturer named by their WMI that will decode
their VINs. eg.. Ford
Australia has a WMI of 6FP I created a worksheet
named '6FP'.

I need a procedure that when users enter the three
character WMI and
click
on search (command button) will activate the
worksheet (named from the
WMI).

I would like to define what the WMI means
(manufacturer name) for
popular
makes that I haven't done a worksheet for yet eg..
6T1 means Toyota
Australia. If user inputs 6T1 I would like it to
return in the active
sheet
in a cell eg.. "6T1 Toyota Australia is not on
decoder".

Else for any other WMI not defined (null). Return
WMI is not recognised
by
decoder.

In Summary : click on command button in active sheet

1. needs to add three cells together (6 , F, P) to
create worksheet name
(6FP)
2. goto to that worksheet eg..
worksheet '6FP'.activate
3. for defined WMI with no worksheet (eg. 6T1) needs
to display in
active
sheet not on decoder
4. for any other undefined WMI (null) needs to
display in active sheet
not
recognised by decoder


Can anyone help me?












.