Since this code is behind the worksheet, you don't need to declare wks3 or set
it to the Activesheet. You can use the Me keyword--Me is the thing that owns
the code--in this case the worksheet.

And I'm not sure if this is a typo, but you're returning column 11 twice--for D5
and D6. Did you really want this?

And when your code makes changes to the worksheet, it'll fire the _change
event. To turn this kind of recursion off, you can disable events (and
re-enable them when you're done making changes.

And when there is no match in the first column, res won't be a string--it'll be
an error value. Like #n/a in a worksheet. So instead of declaring Res as a
String, you should declare it as a Variant.

This worked ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim res As Variant 'could be an error
Dim wks1 As Worksheet
Dim wks2 As Worksheet

Set wks1 = Worksheets("Status Log")
Set wks2 = Worksheets("Setup Sheet")
Set rng = wks1.Range("SVDBStatusLog") 'multiple column database

If Target.Address = "$D$1" Then
res = Application.VLookup(Target, rng, 1, False)
Application.EnableEvents = False
If IsError(res) Then
Me.Range("D2:D6").Value _
= "Not in Database, manual entry required"
Else
Me.Range("D2").Value = Application.VLookup(Target, rng, 4, False)
Me.Range("D3").Value = Application.VLookup(Target, rng, 5, False)
Me.Range("D4").Value = wks2.Range("Job_Name").Value
Me.Range("D5").Value = Application.VLookup(Target, rng, 11, False)
'Column 11 again???
Me.Range("D6").Value = Application.VLookup(Target, rng, 11, False)
End If
Application.EnableEvents = True
End If
End Sub

Casey wrote:

Hi.
I have a Worksheet Change routine that doesn't do anything when the
Target range is changed. When I run the any of the lines of code after
the Else statement in the Immediate Window, it produces a 424 error
Object required. I'm just not seeing/understanding the problem. Need
some help please

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As String
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

Set wks1 = Worksheets("Status Log")
Set wks2 = Worksheets("Setup Sheet")
Set wks3 = ActiveSheet
Set rng = wks1.Range("SVDBStatusLog") 'multiple column database

If Target.Address = "$D$1" Then
res = Application.VLookup(Target, rng, 1, False)
If IsError(res) Then
wks3.Range("D2:D6").Value = "Not in Database, " & _
"manual entry required"
Else
wks3.Range("D2").Value = Application.VLookup(Target, _
rng, 4, False)
wks3.Range("D3").Value = Application.VLookup(Target, _
rng, 5, False)
wks3.Range("D4").Value = wks2.Range("Job_Name")
wks3.Range("D5").Value = Application.VLookup(Target, _
rng, 11, False)
wks3.Range("D6").Value = Application.VLookup(Target, _
rng, 11, False)
End If
End If

End Sub

--
Casey

------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=572420

--

Dave Peterson