Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named range list | Excel Discussion (Misc queries) | |||
List Box - For Input Range can I use named range in another workbo | Excel Worksheet Functions | |||
Multiple instances of a named range, not in the list | Excel Discussion (Misc queries) | |||
How can I use a named range for "pick from drop-down list"? | Excel Discussion (Misc queries) | |||
Setting named range in VBA- how to set as formula/reference instead of text string? | Excel Programming |