All,

Below is a macro for updating a validation list. It works fine for one list
but I have several lists
on my worksheet ..they are in columns D,E,F,I,J, and K...

Can someone help me modify the macro so that I can use it for all of my
validaton lists?

Each list has the offset parmameters attached to their defined names: example:
=OFFSET(Lookup!$C$2,0,0,COUNTA(Lookup!$C:$C),1)

All are on my lookup page in columns C,D,E,F,G,H
The macro works for my list called "Platforms"


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lookup")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Pl atforms"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("C" & i).Value = Target.Value
ws.Range("Platforms").Sort Key1:=ws.Range("C2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub


Thank you for your help

There's a sample file here that updates the appropriate validation list:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for Update Multiple Validation Lists'


Dmorri254 wrote:
All,

Below is a macro for updating a validation list. It works fine for one list
but I have several lists
on my worksheet ..they are in columns D,E,F,I,J, and K...

Can someone help me modify the macro so that I can use it for all of my
validaton lists?

Each list has the offset parmameters attached to their defined names: example:
=OFFSET(Lookup!$C$2,0,0,COUNTA(Lookup!$C:$C),1)

All are on my lookup page in columns C,D,E,F,G,H
The macro works for my list called "Platforms"


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lookup")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Pl atforms"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("C" & i).Value = Target.Value
ws.Range("Platforms").Sort Key1:=ws.Range("C2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub


Thank you for your help







--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you so much I wil try this...

David

"Debra Dalgleish" wrote:

There's a sample file here that updates the appropriate validation list:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for Update Multiple Validation Lists'


Dmorri254 wrote:
All,

Below is a macro for updating a validation list. It works fine for one list
but I have several lists
on my worksheet ..they are in columns D,E,F,I,J, and K...

Can someone help me modify the macro so that I can use it for all of my
validaton lists?

Each list has the offset parmameters attached to their defined names: example:
=OFFSET(Lookup!$C$2,0,0,COUNTA(Lookup!$C:$C),1)

All are on my lookup page in columns C,D,E,F,G,H
The macro works for my list called "Platforms"


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lookup")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Pl atforms"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("C" & i).Value = Target.Value
ws.Range("Platforms").Sort Key1:=ws.Range("C2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub


Thank you for your help







--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html