Hi,

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

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

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

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

Using an example below, the Qty-On-Hand column is where I Vlookup th
results from another worksheet. I do not want to us
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 anothe
Vlookup formula in the first "#N/A" (example here is in row 4), th
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 7

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