add new row to validation list using VBA

[nasser]

hello,
I'm using Validation list in my sheet, and in case I've got a new item not in the list, the below code helping me to just type it and it will automatically added to the validation list,
but i face 2 problems which is :
First: I want the new item to be added in a new row, not just a new cell in the column.
Second : I use 2 worksheets for validation lists which (List - Subs) and I want to effect only Subs to add a new row, "List" is working fine by adding a new cell only
here is the code

Please Login or Register  to view this content.

thank you in advance

[MarvinP]

Hey Nasser,

The way to add a new value to a validation list is by using Dynamic Named Ranges. Read about them at:

http://analyticsdemystified.com/exce...ns-done-right/
http://www.myonlinetraininghub.com/e...ata-validation

[nasser]

Looks I was asking the wrong question , here is my work space
sheet1 "List"
Place Color
Alex. Black
Cairo Blue
Giza Green
Haram Red
Tiba Yellow

Sheet2 "Subs"
Acc.No. Contractor Name Code
1 Frank 987
2 George 876
3 Fadi
4 Nader
5 Kimo 543
6 Naser 432

Name Ranges
Acc.No.=OFFSET(Subs!$A$2,0,0,COUNTA(Subs!$A:$A),1)
colors =OFFSET(List!$B$2,0,0,COUNTA(List!$B:$B),1)
place=OFFSET(List!$A$2,0,0,COUNTA(List!$A:$A),1)

Work Sheet
Place Color Acc. No. Contractor Code
Cairo Red 1 Frank 987
Giza Yellow 5 Kimo 543
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

Formula in Contractor Column (D2) = INDEX(Subs!B:B,MATCH(C2,Subs!A:A,0))
Formula in Code Column (E2) = INDEX(Subs!C:C,MATCH(C2,Subs!A:A,0))

Validation list in Column A = place
Validation list in Column B = colors
Validation list in Column C = Acc.No.
First Question is that I want the code which I sent in the begining to work for both sheets (Subs - List)
Second Question is that if the Acc.No. is not in the list Then I will add it manual and it should automatically added to the "Subs", and that means I should also manually add the Contractor and the code instead of the formula, and also should be in same row with the Acc.No.
OR if the code is not already in the list like in the first sheet under (Fadi and Nader) so once I add it manually in the Work sheet, it should be added to the List