I have the following code for a combobox lookup but it will not find the
value when the combox text is a number:

Private Sub ComboBox1_Click()

Dim nMatch As Long
With ComboBox1
If .Text < "" Then
On Error Resume Next
nMatch = WorksheetFunction.Match(CStr(.Text),
Worksheets("Produced").Range("A3:A500"), 0)
On Error GoTo 0
If nMatch < 0 Then

TextBox1.Text = WorksheetFunction.VLookup(CStr(ComboBox1.Text), Worksheets("
Produced").Range("A3:Z500"), 2, False)

Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End Sub


Cheers
Greg

Gregor,

Try this version

Private Sub ComboBox1_Click()
Dim nMatch As Long
With ComboBox1
If .Text < "" Then
On Error Resume Next
If IsNumeric(.Value) Then
nMatch = WorksheetFunction.Match(CDbl(.Text),
Worksheets("Produced").Range("A3:A500"), 0)
Else
nMatch = WorksheetFunction.Match(.Text,
Worksheets("Produced").Range("A3:A500"), 0)
End If
On Error GoTo 0
If nMatch < 0 Then

If IsNumeric(.Value) Then
TextBox1.Text = WorksheetFunction.VLookup(CDbl(.Value),
_
Worksheets("Produced").Range("A3:Z500"), 2, False)
Else
TextBox1.Text = WorksheetFunction.VLookup(.Value, _
Worksheets("Produced").Range("A3:Z500"), 2, False)
End If
Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End With
End Sub


--
HTH

-------

Bob Phillips
"gregork" wrote in message
...
I have the following code for a combobox lookup but it will not find the
value when the combox text is a number:

Private Sub ComboBox1_Click()

Dim nMatch As Long
With ComboBox1
If .Text < "" Then
On Error Resume Next
nMatch = WorksheetFunction.Match(CStr(.Text),
Worksheets("Produced").Range("A3:A500"), 0)
On Error GoTo 0
If nMatch < 0 Then

TextBox1.Text = WorksheetFunction.VLookup(CStr(ComboBox1.Text),
Worksheets("
Produced").Range("A3:Z500"), 2, False)

Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End Sub


Cheers
Greg

Many thanks Bob....working well.

Regards
Gregor

"Bob Phillips" wrote in message
...
Gregor,

Try this version

Private Sub ComboBox1_Click()
Dim nMatch As Long
With ComboBox1
If .Text < "" Then
On Error Resume Next
If IsNumeric(.Value) Then
nMatch = WorksheetFunction.Match(CDbl(.Text),
Worksheets("Produced").Range("A3:A500"), 0)
Else
nMatch = WorksheetFunction.Match(.Text,
Worksheets("Produced").Range("A3:A500"), 0)
End If
On Error GoTo 0
If nMatch < 0 Then

If IsNumeric(.Value) Then
TextBox1.Text =
WorksheetFunction.VLookup(CDbl(.Value),
_
Worksheets("Produced").Range("A3:Z500"), 2, False)
Else
TextBox1.Text = WorksheetFunction.VLookup(.Value, _
Worksheets("Produced").Range("A3:Z500"), 2, False)
End If
Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End With
End Sub


--
HTH

-------

Bob Phillips
"gregork" wrote in message
...
I have the following code for a combobox lookup but it will not find the
value when the combox text is a number:

Private Sub ComboBox1_Click()

Dim nMatch As Long
With ComboBox1
If .Text < "" Then
On Error Resume Next
nMatch = WorksheetFunction.Match(CStr(.Text),
Worksheets("Produced").Range("A3:A500"), 0)
On Error GoTo 0
If nMatch < 0 Then

TextBox1.Text = WorksheetFunction.VLookup(CStr(ComboBox1.Text),
Worksheets("
Produced").Range("A3:Z500"), 2, False)

Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End Sub


Cheers
Greg