424 error on a Worksheet Change Macro

[Casey]

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("D26").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

 

[Dave Peterson]

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("D26").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]

Dave,
Thanks. Works perfectly. This code is a modification of a post by To
Ogilvy and of course he had the res varible declared as a variant.
must of changed it in my flayling attempt to get it to work. I reall
appreciate the code fix and the explainations.
And yea, returning column 11 twice is what I needed, it is an intege
tracking number for construction plans and specification manuals
Normally, these are issued in matching pairs, but precisely because m
project manager have a knack for losing or mis-numbering there bi
packages is why I'm using code instead of cell formulas to populat
these cells, so manual corrections can be made. Welcome to my world
boring huh.
Many thanks Dave