424 error on a Worksheet Change Macro

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