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
|