VBA Code

  • Thread starter Thread starter Don Rountree
  • Start date Start date
D

Don Rountree

I have used VBA in Access, but am not at familiar with the
syntax for Excel. I want to be able to enter an item
number in a cell (A1)in (Sheet1) and when I hit the Enter
key, I want to perform a VLookup on the value in (A1) and
return the item description from (Sheet2) in cell (B2) in
(Sheet1). I hope I have posed my problem clearly enough.
I want to do this with VBA code so that no matter what row
I input an item number on in column A on Sheet1, the code
will find the value on Sheet 2 and paste the results in
the same row in column B. Any help would be appreciated.

Don Rountree
 
I know you said you want to use code, but formulas are better for this

=if(A1="","",Vlookup(A1,Sheet2!$A$1:$B$200,2,False))

then drag fill down the column.

Regards,
Tom Ogilvy
 
Don,

Like Tom says - the formula is easier.

But if you insist on code - use the worksheet change event.

Right click on the sheet tab and select 'View Code'
This will take you to the VBE and the sheet module (not a standard module).
There are 2 selection boxes at the top. The one on the left will be set for
'Worksheet'. Change the one on the right to 'Change'.

Between 'Sub' and 'End Sub' put the following code.

On Error Resume Next
If Target.Column = 1 and Len(Target) > 0 Then
Cells(Target.Row,2) = Worksheetfunction.Vlookup(Target,Sheets _
("Sheet2").Range("$A$1:$B$200"),2,False)
End If

Change 'Sheet2' to the name of your worksheet. Adjust the range to fit your
lookup table.
Be aware that the lookup returns #N/A if there isn't a match and the macro
could error. The On Error Resume Next will take care of that.

This should get you started.

Watch for other errors...
 
Back
Top