Hello D,

Do I understand correctly...
Go down column D and for each error value copy the Column C
value to Column F. However, if the value in Column C is already in
column F then do nothing?

Regards,
Jim Cone
San Francisco, USA

"Dolemite"

wrote in message

Alright, once again, I am having an issue with syntax, and can't quite
get this right.

Basically I am wanting a listing of unique units that don't have a
corresponding value in a table on another sheet. I have everything
else being done, but can't get the unique list going through a VB
Script...here is a little (or a Lot) of info for background....

I have a worksheet that gets populated by "units"/Codes (whatever) that
are entered by a user in column C. When they are actually entering the
values the worksheet does nothing significant. I have an "update"
button at the top of the sheet that when clicked it starts a macro that
fills in several other columns on the sheet according to what is entered
in columns B & C (B contains quantity, C contains unit). One of the
equations that is entered into a column is a lookup/match formula that
returns the cost for the particular unit that is listed in column C.
However, there are times when new units are entered into this column,
but haven't been entered into the master listing. So obviously the
lookup formula will return an error. I am trying to get a listing of
all the UNIQUE units/codes to list in another column. I have been
trying to do this with the countif statement but can only seem to get
it to work using explicit cell references. But I can't use explicit
cell references with the manner in which I am using it.

For Example...given the following sheet setup (not exactly like mine,
but gets the point across):

.........A.................B...................C.. .................D................E............... ........F
........No...........Qty.................Unit..... ........Cost.............Total................Miss ing
.........1..............1.....................A1.. ..........
.........2..............1.....................AA1. .........
.........3..............2.....................A2.. ..........
.........3..............5.....................AA1. .........


If AA1 and A2 are missing costs and return an error in column D when I
push the update button, I would like to have the following result

.........A.................B...................C.. .................D................E............... ........F
........No...........Qty.................Unit..... ........Cost.............Total................Miss ing
.........1..............1.....................A1.. ..............$5.00..........$5.00................ AA1
.........2..............1.....................AA1. ..............N/A.............$0.00................A2
.........3..............2.....................A2.. ...............N/A.............$0.00................

If a unit is missing the cost I am only wanting it to be listed only 1
time. I figured that a countif statement would be more efficient than
a for/if combo for the entire listing (the listing can get extremely
lengthy) Also, there is also information above this listing (in other
words "No." does not reside in cell A1, actually right now it is in
A11)

I have a range defined so that the first cell of the range is C11 where
my first unit is listed. I am then using a for loop to populate the
remaining columns where there is value in Column C. This all works
fine. I just can't get my unique values extracted from the listing in
column C to put in my Missing column.

- snip -