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

setting a range using a named list in vba
 

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

setting a range using a named list in vba
 

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

setting a range using a named list in vba
 

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

setting a range using a named list in vba
 

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

setting a range using a named list in vba
 

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

setting a range using a named list in vba
 

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

setting a range using a named list in vba
 

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" wrote in message
...
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

setting a range using a named list in vba
 

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" wrote in message
...
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

setting a range using a named list in vba
 

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" wrote in message
...
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

setting a range using a named list in vba
 

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" wrote in message
...
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

setting a range using a named list in vba
 

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" wrote in message
...
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

setting a range using a named list in vba
 

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" wrote in message
...
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