Xrull
03-30-2009, 08:25 PM
I need to know what I'm doing wrong here.
This code works:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
If Target.Column > 1 Then Exit Sub
Set rng2 = Sheets("Data").Range("A2:E2000")
For Each rng In Target
If Len(rng) > 0 Then
rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
Else
rng.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng
End Sub
I want to have 2 looks ups or more, but I keep getting a message when I try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
If Target.Column = 1 Then
Set rng2 = Sheets("Data").Range("A2:E2000")
For Each rng In Target
If Len(rng) > 0 Then
rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
Else
rng.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng
If Target.Column = 6 Then
Set rng4 = Sheets("Data").Range("G2:H2000")
For Each rng3 In Target
If Len(rng3) > 0 Then
rng3.Offset(, 1) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
rng3.Offset(, 2) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
Else
rng3.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng3
End Sub
How can I get the last code to work?
Thanks,
Xrull
This code works:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
If Target.Column > 1 Then Exit Sub
Set rng2 = Sheets("Data").Range("A2:E2000")
For Each rng In Target
If Len(rng) > 0 Then
rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
Else
rng.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng
End Sub
I want to have 2 looks ups or more, but I keep getting a message when I try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
If Target.Column = 1 Then
Set rng2 = Sheets("Data").Range("A2:E2000")
For Each rng In Target
If Len(rng) > 0 Then
rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
Else
rng.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng
If Target.Column = 6 Then
Set rng4 = Sheets("Data").Range("G2:H2000")
For Each rng3 In Target
If Len(rng3) > 0 Then
rng3.Offset(, 1) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
rng3.Offset(, 2) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
Else
rng3.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng3
End Sub
How can I get the last code to work?
Thanks,
Xrull