setting a range using a named list in vba

[Guest]

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

 

[Guest]

fyi my error is:

Run Time 1004
Method 'Range' of object '_Worksheet' faliled

 

[Guest]

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?

 

[Guest]

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

 

[Dave Peterson]

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

 

[Guest]

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.

 

[Guest]

tried it with

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

and get

error '1004'

Application-defined or object-defined error

 

[Peter T]

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

 

[Guest]

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?

 

[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.

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?

 

[Guest]

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]

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.

 

[Guest]

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....)