copy down to all Vlookup "#N/A" cells ONLY (in the same column)
 

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

copy down to all Vlookup "#N/A" cells ONLY (in the same column)
 

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/