That means the vlookup can't find the value of Range("attn") in the lookup
range.

You need to handle the error

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim company As Range
Dim database As Range
Dim attn As Range

If Target.Address = Range("attn").Address Then
On Error Resume Next
Range("company") = Application.WorksheetFunction _
.VLookup(Range("attn"),Range("database"), 2, False)
End If
On Error goto 0
End Sub

Another problem can be that if these named ranges are not on the worksheet
containing the code, they need to be qualified with the worksheet on which
they are located.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim company As Range
Dim database As Range
Dim attn As Range
Dim sh1 as Worksheet
Dim sh2 as Worksheet
Set Sh1 = Worksheets("Database")
Set sh2 = Worksheets("Sheet3")

If Target.Address = Range("attn").Address Then
On Error Resume Next
sh2.Range("company") = Application.WorksheetFunction _
.VLookup(Range("attn"),sh1.Range("database"), 2, False)
End If
On Error goto 0
End Sub

as an example.

--
Regards,
Tom Ogilvy



"gig" wrote in message
ups.com...
The error message I keep getting is:

Run time error '1004':

Method 'Range' of object'_worksheet' failed


Thanks