Vlookup in a Macro ?

Posted by Phil Ridley on January 21, 2002 12:20 AM

I have a macro that is like this :

Private Sub Worksheet_Change(ByVal Target As Range)

input = Target.Value
newinput = VLookup(userinput, data, 2, False)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End Sub

The idea is to enter a part number in a cell and have the part number replaced by the description of the part that is in a list in another sheet in the same workbook. However, Vlookup does not seem to work in a macro. Can someone offer me a hint ?

Thanks in advance
Phil.

Posted by Stephen Hoadley on January 21, 2002 12:40 AM

Try This Enter part No in cell a3 in sheet 1 and have the parts list and description in sheet 2

Application.CutCopyMode = False

Range("b3").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C[-1]:R35C,2,FALSE)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
Range("A3").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Range("A3").Select
End Sub

Posted by Phil Ridley on January 21, 2002 1:03 AM

Yeah, I thought of this, but I have data in the sheet surrounding these cells. I forgot to mention that the macro is defined as an event. All other cells in all other sheets are formulas that are locked for editing, so it has to take place in the cell the data is entered in.

Phil. Range("b3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C[-1]:R35C,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Cut Range("A3").Select ActiveSheet.Paste Columns("A:A").EntireColumn.AutoFit Range("A3").Select

Posted by Bariloche on January 21, 2002 5:17 AM

Phil,

I believe the following:

input = Target.Value
newinput = VLookup(userinput, data, 2, False)

should actually be:

input = Target.Value
newinput = VLookup(input, data, 2, False)

I've bolded my change so that its easier to see.

If I'm interpreting your code correctly, and you don't have a variable named "userinput" defined above the snippet you provided, then this change should work for you.

enjoy