VBA vlookup from another sheet?

[bkuu]

I have this code working at the moment

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("D2:D500")) Is Nothing Then
Application.EnableEvents = False 'Disables events to prevent endless loop

On Error GoTo Finalize 'Re-enable events

Target.Value = Application.VLookup(Target.Value, Me.Range("Q:R"), 2, False)
End If

Finalize:
Application.EnableEvents = True
End Sub

However, I'd like to change the vlookup source ("Q:R") to same the column but on another sheet ("Sheet2"). I have been trying to input Worksheet("Sheet2") any where in the code but I don't think I'm doing it correctly. Any ideas?

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D2:D500")) Is Nothing Then
Application.EnableEvents = False 'Disables events to prevent endless loop

On Error GoTo Finalize 'Re-enable events

Dim rng as Range
Set rng = Sheets("Sheet2").Range("Q:R")
Target.Value = Application.VLookup(Target.Value, rng, 2, False)
End If

Finalize:
Application.EnableEvents = True
End Sub

BTW, you can get rid of the "Me.". It serves no purpose here.

 

[Cubist]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("D2:D500")) Is Nothing Then
        Application.EnableEvents = False 'Disables events to prevent endless loop
        On Error GoTo Finalize 'Re-enable events
        Target.Value = Application.VLookup(Target.Value, ThisWorkbook.Sheets("Sheet2").Range("Q:R"), 2, False)
    End If
 
Finalize:
    Application.EnableEvents = True
End Sub

 

[bkuu]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D2:D500")) Is Nothing Then
Application.EnableEvents = False 'Disables events to prevent endless loop

On Error GoTo Finalize 'Re-enable events

Dim rng as Range
Set rng = Sheets("Sheet2").Range("Q:R")
Target.Value = Application.VLookup(Target.Value, rng, 2, False)
End If

Finalize:
Application.EnableEvents = True
End Sub

BTW, you can get rid of the "Me.". It serves no purpose here.

works! Thank you

 

[bkuu]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("D2:D500")) Is Nothing Then
        Application.EnableEvents = False 'Disables events to prevent endless loop
        On Error GoTo Finalize 'Re-enable events
        Target.Value = Application.VLookup(Target.Value, ThisWorkbook.Sheets("Sheet2").Range("Q:R"), 2, False)
    End If
 
Finalize:
    Application.EnableEvents = True
End Sub

thanks for the reply. Your method works as well. Looks like I almost had it , just got the Workbooksheet part wrong

 

[Joe4]

You are welcome.
Glad we were able to help!