PDA

View Full Version : Solved: Nesting in a Private Sub Worksheet_Change



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

Bob Phillips
03-31-2009, 12:12 AM
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

ElseIf 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 If
End Sub

Xrull
03-31-2009, 03:24 AM
xld,
That's what I was looking for.
Xrull