I tried your code and I got an error at

set n=.range(.range(C))

rather than Set x=range("CustomerList") (fine for me!)

Should this be

Set n=.range(c) ?


I can only suggest you check definition of "CustomerList" - possible typing
error in name?




"Gixxer_J_97" wrote:

> fyi my error is:
>
> Run Time 1004
> Method 'Range' of object '_Worksheet' faliled
>
>
> "Gixxer_J_97" wrote:
>
> > when a customer is chosen on my order worksheet, the change event triggers
> > the following code:
> >
> > <BEGIN VBA CODE>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> >
> > ' Column for Notes is AC (29)
> > ' Starting row is 8
> >
> >
> > If Target.Column = 3 Then
> > If Target.Row = 8 Then
> > Dim n, x As Range, off As Integer, c As String
> > With Sheets("Work Order")
> > Set x = Range("CustomerList")
> > End With
> > off = 7 + Application.WorksheetFunction.Match(Range("C8"), x, 0)
> > c = "$AC$" & off
> > With Sheets("Customers")
> > Set n = .Range(.Range(c))
> > End With
> > ' popup notes about customer
> > MsgBox n.Value
> > End If
> > End If
> >
> > End Sub
> >
> > <END VBA CODE>
> >
> > what it should do is look in C8, and then find at which location that
> > customer is in the list (Thanks to Tom for the Match help!). Then add 7 to it
> > - as the first entry in my customer list is in row 8.
> >
> > i am getting an error on
> > Set x = Range("CustomerList")
> >
> > CustomerList is a named list in my workbook - in fact it is the source for
> > cell C8
> >
> > what am i missing?
> >
> > thanks!
> >
> > J