I tried it again and still I get the error in the same place.
I also checked the naming and spelling - it's all correct
(even went to the data validation source of C8 and copied the name from there)

still erroring (1004) out at
Set x = Range("CustomerList")

i even tried
Set x = .Range("CustomerList")

..... i'm stumped


"Toppers" wrote:

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