VBA Program works some times and some times gives me a Run-time error '1004'?

nathansav, Yes I see exactly what you are talking about. I have a defined Name in my spread sheet called ProductList=OFFSET(LookupList!$A$2,0,0,COUNTA(LookupList!$A:$A)-1,1) . The reason why I could get my worksheet to run fine a few tims and then it would crash, is because I was deleting my anchor cell. My defined name then looked like this ProductList=OFFSET(LookupList!Ref#1,0,0,COUNTA(LookupList!$A:$A)-1,1). The $A$2 no longer existed in the formula, thus the error 1004. Well I did'nt bank on this problem! Now I have got to come up with a work around. My first thought was to remove the absolute reference, change $A$2 to A2; however, that did not work, I recived the same results. Maybe instead of deleting and inserting, I could delete by draging my product list up over the top of my unwanted value. And insert by moving the product list from the point of insertion down by one and insert the new product above it. I'm not sure if that wll alter my anchor point or not. Do you have a suggestion to try before a rewrite this module?
Thanks, redsab

Arkadi,
That certainly would correct my problem; however, I am not sure how to do that task. I will do some research on that and see if I can come up with code to modify the Defined Name in my spreadsheet. Thanks for the good suggestion, I will let you know what I come up with.
redsab

Hi Redsab,
maybe so

Please Login or Register  to view this content.

Hi nilem,

That does delete my product number, but it also displaces the ancor cell when I delete the first item in the list and causes the same error 1004. However, I came up with the following as Arkadi suggested.

Please Login or Register  to view this content.

I have added this at the end of the subroutine for the Add and Delete button. Thanks for your code nilem and to everyone that helped!

redsab