Cell change event
 

I keep getting an error message when range("attn") is changed. If I
delete the vlookup formula line and put something like msgbox "hello"
it will work fine. But what puzzles me is that the same vlookup line
formula works in another program with no problems. Can anyone tell me
what I'm doing wrong?

Thanks,
Greg


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

Range("company") = Application.WorksheetFunction.VLookup(Range("attn" ),
Range("database"), 2, False)
End If

End Sub

Cell change event
 

gig,
what error message are you recieving?

"gig" wrote:

I keep getting an error message when range("attn") is changed. If I
delete the vlookup formula line and put something like msgbox "hello"
it will work fine. But what puzzles me is that the same vlookup line
formula works in another program with no problems. Can anyone tell me
what I'm doing wrong?

Thanks,
Greg


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

Range("company") = Application.WorksheetFunction.VLookup(Range("attn" ),
Range("database"), 2, False)
End If

End Sub

Cell change event
 

The error message I keep getting is:

Run time error '1004':

Method 'Range' of object'_worksheet' failed


Thanks

Cell change event
 

it sounds like one of your named ranges is not available, generally that
error means that Excel cannot find or cannot select the specified range, also
you might want to consider not naming your ranges in your code to the same
exact names you have in your spreadsheet defined names. So make sure to check
that named ranges are not mispelled.

"gig" wrote:

The error message I keep getting is:

Run time error '1004':

Method 'Range' of object'_worksheet' failed


Thanks

Cell change event
 

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

Cell change event
 

Thanks for your input Ben and Tom. I need to study this in detail now
and see what's going on.

Much appreciated,

Greg