Worksheet Change function with VLookup

[jnathan]

I am attempting to write a bit of code so that Vlookup is called when a particular cell in my spreadsheet is populated.

Although it seems to be working, it is only pulling in the data in row 1.

An example of the code is below.

Any help would be greatly appreciated.

Justin

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>

If Target.Row <> 1 Then</SPAN>
If Target.Column = 2 Then</SPAN>
If IsNumeric(Target.Value) Then</SPAN>
Target.Offset(0, 1) = Evaluate("=VLOOKUP($B:$B,'[Trades Alerts.xlsx]Sheet1'!$A:$U,2,FALSE)")</SPAN>
Target.Offset(0, 2) = Evaluate("=VLOOKUP($B:$B,'[Trades Alerts.xlsx]Sheet1'!$A:$U,4,FALSE)")</SPAN>
Target.Offset(0, 3) = Evaluate("=VLOOKUP($B:$B,'[Trades Alerts.xlsx]Sheet1'!$A:$U,5,FALSE)")</SPAN>
Target.Offset(0, 4) = Evaluate("=VLOOKUP($B:$B,'[Trades Alerts.xlsx]Sheet1'!$A:$U,12,FALSE)")</SPAN>
Target.Offset(0, 5) = Evaluate("=VLOOKUP($B:$B,'[Trades Alerts.xlsx]Sheet1'!$A:$U,14FALSE)")</SPAN>
Target.Offset(0, 6) = Evaluate("=VLOOKUP($B:$B,'[Trades Alerts.xlsx]Sheet1'!$A:$U,16,FALSE)")</SPAN>
End If</SPAN>
End If</SPAN>
End If</SPAN>
End Sub</SPAN>

 

[William Day]

While using an entire column as a lookup value on the worksheet will work when copied down, when used like in your code it will only ever look up the first value in the column (cell B1).

This should work for you.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rRng As Range

    Set rRng = Workbooks("Trades Alerts.xlsx").Worksheets("Sheet1").Range("A:U")

    If Target.Row <> 1 Then
        If Target.Column = 2 Then
            If IsNumeric(Target.Value) Then
                Target.Offset(0, 1) = Application.VLookup(Target.Value, rRng, 2, False)
                Target.Offset(0, 2) = Application.VLookup(Target.Value, rRng, 4, False)
                Target.Offset(0, 3) = Application.VLookup(Target.Value, rRng, 5, False)
                Target.Offset(0, 4) = Application.VLookup(Target.Value, rRng, 12, False)
                Target.Offset(0, 5) = Application.VLookup(Target.Value, rRng, 14, False)
                Target.Offset(0, 6) = Application.VLookup(Target.Value, rRng, 16, False)
            End If
        End If
    End If

End Sub

Hope this helps.
WD