So where ya gonna send muh doughnuts? =)

tried that (F5 an the name) and yep - went straight to the range
(even tried copying the name of the range from the VBA code and then F5 ->
paste, and still went to the range of CustomerList.....

tried restarting excel - and rebooting too jic

?!

i'm 'a stumped.





"Dave Peterson" wrote:

> I'd still bet dollars to doughnuts (hmmmmmmm, doughnuts) that it's a spelling
> error.
>
> Back to excel and hit F5.
> Paste CustomerList in that dialog and see what happens.
>
> if you have multiple ranges with the name Customerlist, then you'll want to use
> the:
>
> With worksheets("work order")
> set x = .range("customerlist")
> end with
>
>
>
> Gixxer_J_97 wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>