setting a range using a named list in vba

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

Originally Posted by Gixxer_J_97

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

Try using the following code to determine if in fact your CustomerList range name has been defined:

Sub test()
For Each nme In ActiveWorkbook.Names
MsgBox ("Name: " & nme.Name & " refers to: " & nme.RefersTo)
Next
End Sub

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

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

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

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

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
>

tried it with

With Sheets("Work Order")
set x = .range("CustomerList")
end with

and get

error '1004'

Application-defined or object-defined error



"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
>

Are you sure whatever formula you've used in "Refers to" returns a range

'if a workbook level name
Debug.Print ActiveWorkbook.ActiveWorkbook.Names("CustomerList")

'if a worksheet-level name
Debug.Print ActiveWorkbook.Worksheets("Work Order").Names("CustomerList")

should return the address, something like:
='Work Order'!$A$1:$A$8

Regards,
Peter T

"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:926A7395-451B-4507-9B69-0436A10BD9D3@microsoft.com...
> tried it with
>
> With Sheets("Work Order")
> set x = .range("CustomerList")
> end with
>
> and get
>
> error '1004'
>
> Application-defined or object-defined error
>
>
>
> "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
> >

CustomerList is defined as
=OFFSET(Customers!$C$8,0,0,COUNTA(Customers!$C:$C),1)

and the debug.print gave the same error as the set x did

is the way i'm defining my name my problem?

"Peter T" wrote:

> Are you sure whatever formula you've used in "Refers to" returns a range
>
> 'if a workbook level name
> Debug.Print ActiveWorkbook.ActiveWorkbook.Names("CustomerList")
>
> 'if a worksheet-level name
> Debug.Print ActiveWorkbook.Worksheets("Work Order").Names("CustomerList")
>
> should return the address, something like:
> ='Work Order'!$A$1:$A$8
>
> Regards,
> Peter T
>
> "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> news:926A7395-451B-4507-9B69-0436A10BD9D3@microsoft.com...
> > tried it with
> >
> > With Sheets("Work Order")
> > set x = .range("CustomerList")
> > end with
> >
> > and get
> >
> > error '1004'
> >
> > Application-defined or object-defined error
> >
> >
> >
> > "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
> > >
>
>
>

Then maybe:

With Sheets("Work Order")
set x = .range("CustomerList")
end with

should be:

With Sheets("Customers")
set x = .range("CustomerList")
end with

And if that doesn't help, if you put:
=counta(customers!c:c)
in an empty cell, what do you get returned?

And that dynamic range looks kind of funny if you have stuff in C1:C7 of the
Customers worksheet.




Gixxer_J_97 wrote:
>
> CustomerList is defined as
> =OFFSET(Customers!$C$8,0,0,COUNTA(Customers!$C:$C),1)
>
> and the debug.print gave the same error as the set x did
>
> is the way i'm defining my name my problem?
>
> "Peter T" wrote:
>
> > Are you sure whatever formula you've used in "Refers to" returns a range
> >
> > 'if a workbook level name
> > Debug.Print ActiveWorkbook.ActiveWorkbook.Names("CustomerList")
> >
> > 'if a worksheet-level name
> > Debug.Print ActiveWorkbook.Worksheets("Work Order").Names("CustomerList")
> >
> > should return the address, something like:
> > ='Work Order'!$A$1:$A$8
> >
> > Regards,
> > Peter T
> >
> > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > news:926A7395-451B-4507-9B69-0436A10BD9D3@microsoft.com...
> > > tried it with
> > >
> > > With Sheets("Work Order")
> > > set x = .range("CustomerList")
> > > end with
> > >
> > > and get
> > >
> > > error '1004'
> > >
> > > Application-defined or object-defined error
> > >
> > >
> > >
> > > "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
> > > >
> >
> >
> >

--

Dave Peterson

That was so simple it was dumb (on my part that is)

thanks Dave!! (So where do i send yer doughnuts?)

and (please tell me if i'm wrong) I have the dynamic range starting from C8
(C1-C6 are empty, and C7 is the header of the column. The actual data i want
to use starts in C8:C??), which is why my formula is
=OFFSET($C$8,0,0,COUNTA($C:$C),1)


"Dave Peterson" wrote:

> Then maybe:
>
> With Sheets("Work Order")
> set x = .range("CustomerList")
> end with
>
> should be:
>
> With Sheets("Customers")
> set x = .range("CustomerList")
> end with
>
> And if that doesn't help, if you put:
> =counta(customers!c:c)
> in an empty cell, what do you get returned?
>
> And that dynamic range looks kind of funny if you have stuff in C1:C7 of the
> Customers worksheet.
>
>
>
>
> Gixxer_J_97 wrote:
> >
> > CustomerList is defined as
> > =OFFSET(Customers!$C$8,0,0,COUNTA(Customers!$C:$C),1)
> >
> > and the debug.print gave the same error as the set x did
> >
> > is the way i'm defining my name my problem?
> >
> > "Peter T" wrote:
> >
> > > Are you sure whatever formula you've used in "Refers to" returns a range
> > >
> > > 'if a workbook level name
> > > Debug.Print ActiveWorkbook.ActiveWorkbook.Names("CustomerList")
> > >
> > > 'if a worksheet-level name
> > > Debug.Print ActiveWorkbook.Worksheets("Work Order").Names("CustomerList")
> > >
> > > should return the address, something like:
> > > ='Work Order'!$A$1:$A$8
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > > news:926A7395-451B-4507-9B69-0436A10BD9D3@microsoft.com...
> > > > tried it with
> > > >
> > > > With Sheets("Work Order")
> > > > set x = .range("CustomerList")
> > > > end with
> > > >
> > > > and get
> > > >
> > > > error '1004'
> > > >
> > > > Application-defined or object-defined error
> > > >
> > > >
> > > >
> > > > "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
> > > > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

If you hit F5 and then type in Customerlist, don't you end up with an extra cell
at the bottom of the list (in the selected area)?

=OFFSET($C$8,0,0,COUNTA($C:$C)-1,1)

Gets me just the data--no header.


Gixxer_J_97 wrote:
>
> That was so simple it was dumb (on my part that is)
>
> thanks Dave!! (So where do i send yer doughnuts?)
>
> and (please tell me if i'm wrong) I have the dynamic range starting from C8
> (C1-C6 are empty, and C7 is the header of the column. The actual data i want
> to use starts in C8:C??), which is why my formula is
> =OFFSET($C$8,0,0,COUNTA($C:$C),1)
>
> "Dave Peterson" wrote:
>
> > Then maybe:
> >
> > With Sheets("Work Order")
> > set x = .range("CustomerList")
> > end with
> >
> > should be:
> >
> > With Sheets("Customers")
> > set x = .range("CustomerList")
> > end with
> >
> > And if that doesn't help, if you put:
> > =counta(customers!c:c)
> > in an empty cell, what do you get returned?
> >
> > And that dynamic range looks kind of funny if you have stuff in C1:C7 of the
> > Customers worksheet.
> >
> >
> >
> >
> > Gixxer_J_97 wrote:
> > >
> > > CustomerList is defined as
> > > =OFFSET(Customers!$C$8,0,0,COUNTA(Customers!$C:$C),1)
> > >
> > > and the debug.print gave the same error as the set x did
> > >
> > > is the way i'm defining my name my problem?
> > >
> > > "Peter T" wrote:
> > >
> > > > Are you sure whatever formula you've used in "Refers to" returns a range
> > > >
> > > > 'if a workbook level name
> > > > Debug.Print ActiveWorkbook.ActiveWorkbook.Names("CustomerList")
> > > >
> > > > 'if a worksheet-level name
> > > > Debug.Print ActiveWorkbook.Worksheets("Work Order").Names("CustomerList")
> > > >
> > > > should return the address, something like:
> > > > ='Work Order'!$A$1:$A$8
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > > > news:926A7395-451B-4507-9B69-0436A10BD9D3@microsoft.com...
> > > > > tried it with
> > > > >
> > > > > With Sheets("Work Order")
> > > > > set x = .range("CustomerList")
> > > > > end with
> > > > >
> > > > > and get
> > > > >
> > > > > error '1004'
> > > > >
> > > > > Application-defined or object-defined error
> > > > >
> > > > >
> > > > >
> > > > > "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
> > > > > >
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

that is correct - however, i don't want the header to appear in the
validation box. so the extra empty cell at the bottom wasn't a problem.

(but thank you for pointing that out - i was kind of wondering why it was
doing that....)


"Dave Peterson" wrote:

> If you hit F5 and then type in Customerlist, don't you end up with an extra cell
> at the bottom of the list (in the selected area)?
>
> =OFFSET($C$8,0,0,COUNTA($C:$C)-1,1)
>
> Gets me just the data--no header.
>
>
> Gixxer_J_97 wrote:
> >
> > That was so simple it was dumb (on my part that is)
> >
> > thanks Dave!! (So where do i send yer doughnuts?)
> >
> > and (please tell me if i'm wrong) I have the dynamic range starting from C8
> > (C1-C6 are empty, and C7 is the header of the column. The actual data i want
> > to use starts in C8:C??), which is why my formula is
> > =OFFSET($C$8,0,0,COUNTA($C:$C),1)
> >
> > "Dave Peterson" wrote:
> >
> > > Then maybe:
> > >
> > > With Sheets("Work Order")
> > > set x = .range("CustomerList")
> > > end with
> > >
> > > should be:
> > >
> > > With Sheets("Customers")
> > > set x = .range("CustomerList")
> > > end with
> > >
> > > And if that doesn't help, if you put:
> > > =counta(customers!c:c)
> > > in an empty cell, what do you get returned?
> > >
> > > And that dynamic range looks kind of funny if you have stuff in C1:C7 of the
> > > Customers worksheet.
> > >
> > >
> > >
> > >
> > > Gixxer_J_97 wrote:
> > > >
> > > > CustomerList is defined as
> > > > =OFFSET(Customers!$C$8,0,0,COUNTA(Customers!$C:$C),1)
> > > >
> > > > and the debug.print gave the same error as the set x did
> > > >
> > > > is the way i'm defining my name my problem?
> > > >
> > > > "Peter T" wrote:
> > > >
> > > > > Are you sure whatever formula you've used in "Refers to" returns a range
> > > > >
> > > > > 'if a workbook level name
> > > > > Debug.Print ActiveWorkbook.ActiveWorkbook.Names("CustomerList")
> > > > >
> > > > > 'if a worksheet-level name
> > > > > Debug.Print ActiveWorkbook.Worksheets("Work Order").Names("CustomerList")
> > > > >
> > > > > should return the address, something like:
> > > > > ='Work Order'!$A$1:$A$8
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > > > > news:926A7395-451B-4507-9B69-0436A10BD9D3@microsoft.com...
> > > > > > tried it with
> > > > > >
> > > > > > With Sheets("Work Order")
> > > > > > set x = .range("CustomerList")
> > > > > > end with
> > > > > >
> > > > > > and get
> > > > > >
> > > > > > error '1004'
> > > > > >
> > > > > > Application-defined or object-defined error
> > > > > >
> > > > > >
> > > > > >
> > > > > > "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
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>