Got a little challenge any ?

[nrage21]

Thanks to Tom I got the following:

Private Sub Combobox1_Click()
Dim res As Variant
res = Application.Match(CDbl(ComboBox1.Value), _
Worksheets("Sheet3").Range("A1:A20"), 0)
Worksheets("Sheet1").Range("G7").Value = _
Worksheets("Sheet3").Range("B1:B20")(res).Value

End Sub

the following is the range A1:A20 in Sheet3:

...A..............B

North
10000....North st1
10001....North st2
10002...'...you get the point
10003
South
10004
10005
10006
10007
West
10008
'...and so on

the above code only allows numbers to be chosen from the combobox, wh
can the code be tweaked to allow non numeric characters to be selecte
(i.e. "North", "South", etc???

- Larry -
VBA Amateur .... but learning :

 

[Bob Phillips]

Hi Larry,

Here is one way

Private Sub Combobox1_Click()
Dim res As Variant
On Error Resume Next
res = Application.Match(CDbl(ComboBox1.Value), _
Worksheets("Sheet3").Range("A1:A20"), 0)
If Err.Number <> 0 Then
res = Application.Match(ComboBox1.Value, _
Worksheets("Sheet3").Range("A1:A20"), 0)
End If
On Error GoTo 0
Worksheets("Sheet1").Range("G7").Value = _
Worksheets("Sheet3").Range("B1:B20")(res).Value

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[nrage21]

Thanks Bob!
as always so helpful

- Larry -
VBA Amateu