select column b and do
Edit=goto=special and select formulas and errors

This will select all the cells showing #N/A. Now you can enter a new
formula using relative references and entered according to the active cell.

then finish with Ctrl+enter rather than just enter and this will fill all
the selected cells.

--
Regards,
Tom Ogilvy

alienscript wrote in message
...
Hi,

Hope someone can help me with a code for this..

Everyday in my data worksheet I have to Vlookup to spreadsheets
(contain different formats) from 6 different
respondents for various values or answers with reference to my column
of spares PartNumbers.

After the Vlookup, the column will have formula cells populated with
values and "#N/A". My current practice is I copy this whole column of
Vlook-up results, click Edit/Paste-Special and paste them as Values. I
have to do this because subsequently I will
use Auto-Filter in that column for only the "#N/A" and start another
round of Vlookup the "#N/A" cells to the 2nd respondent's worksheet...
and continue with same steps above until the 6th respondent's
worksheet.

If I don't use Paste-Special/Paste-as-values, the steps alternating
between Auto-Filtering and Unfiltering the column will take very long
because the column is still having Vlookup formulas tie to the 6
different worksheets. My data sheet span about 6000 rows make it even
slower..

Using an example below, the Qty-On-Hand column is where I Vlookup the
results from another worksheet. I do not want to use
Copy/Paste-as-values but banking on hope for a code that
can allow Vlookup on ONLY the #N/A cells. ie. when I type in another
Vlookup formula in the first "#N/A" (example here is in row 4), the
code should only execute to copy the Vlookup
formula down to the rest of the other "N/A" cells in the same column,
skipping those that are not "#N/A".

How can this be done ?
Any help on this is very much appreciated.



PartNumbers Qty-On-Hand
1445A 2
1122F 0
R1261Z #N/A
2265B 112
1000A #N/A
1000C #N/A
1002A 9
1000C #N/A
2880A 72


---
Message posted from http://www.ExcelForum.com/