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
>