Hello All,
I am working on some excel VBA code and I am a little - ok - a lot stuck on
a procedure I am trying to create.

I have 2 sheets in the workbook one called "produce code" and one called
"produce name"

On the produce code sheet are drop down boxes in each cell of column A that
contain the produce code, column B is blank. When a user selects a a code I
want to populate the cell beside it (column B) with the name located in the
worksheet "produce name"

The second worksheet ("produce name") has all the codes and names i.e.

Column A Column B
1 Banana
2 Apple
3 Orange

So when the user selects 1 in column A of worksheet "produce code" I am
trying to populate column B with "Banana". I can do this using vbLookup but
I want to do this using vba code.

My thought is putting column A when selected into a variable then looping
through Column A of the second worksheet to find a match and selecting the
value of the cell beside it.

Can anyone please help.

Thank you so much for your time

Dan

To do this in VBA, you can call the Excel VLookup function from withi
VBA

Assume A1 has your list box,
the cell link is B1
you have a range with your lookup table 1 banana, 2 apples etc

the this code will give you what you want

Sub test()
Dim myRange As Range
y = Range("B1").Value
Set myRange = Range("Table")
X = Application.WorksheetFunction.VLookup(y, myRange, 2)
End Sub

Regards

Mat

--
Mallyca
-----------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...fo&userid=3551
View this thread: http://www.excelforum.com/showthread.php?threadid=56178