I HAD A TYPO THE
i typed this (0,1) but had to be this (1,0)

This is how the code figures where to put the new value
the code:
Worksheets("Params").Cells(Rows.Count, 3).End(xlUp).Offset _
(1, 0).Value = NewEntry

the explanation:

Worksheets("Params").Cells(Rows.Count, 3) = this would be the last
cell in column 3 (C - that's where your list is). If you're 2003, then
it would be cell C65355K

..End(xlUp) = this bit would result into the last nonblank (i use the
term loosely here) in the same column C
..Offset(1,0) = this bit gets it to the cell one row below the last
nonblank row and puts the new value in it.

Then, the offset formula in Name defintion just adds it to the list.


On 17 July, 22:53, AB wrote:
Yes, you need to change that:
Worksheets("Params").Cells(Rows.Count, 1).End(xlUp).Offset _
* * * * *(3, 3).Value = NewEntry
to
Worksheets("Params").Cells(Rows.Count, 3).End(xlUp).Offset _
* * * * *(0, 1).Value = NewEntry

I changed the '1' to '3' [Cells(Rows.Count, 3] as it needs to add it
to column 'C' (i.e., 3), i also changed the Offset back to (0,1) as it
needs to stay in the column 3 just one row below the last nonblank
cell.

Does it work now?

On 17 July, 21:24, Ixtreme wrote:



Thansk for your help, I still have a little problem. After I get the
message do you want to add it to the list and if I select Yes, nothing
really happens. What is wrong with my code; did I miss anything? I
don't understand how it knows to what named range it should add the
new value.
I have:

Sheet1 D3 has Validation 'Product'
Params C2 'Product' = columname
Params C3 'Product item 1'
Params C4 'Product item 3'
etc.

I have added the named range 'Product' = =OFFSET(Params!$C$3;0;0;COUNTA
(Params!$C:$C )-1)

I think I have to change this piece: * * Worksheets("Params").Cells
(Rows.Count, 1).End(xlUp).Offset _
* * * * (3, 3).Value = NewEntry