Code Lookup for Number
 

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

Code Lookup for Number
 

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

Code Lookup for Number
 

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