Code Lookup for Number

[gregork]

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

 

[Bob Phillips]

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

 

[gregork]

Many thanks Bob....working well.

Regards
Gregor

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