Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a vlookup formula without changing the "table_array" p | Excel Worksheet Functions | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
Can I copy the formatting of a cell in a IF Fucntion "VLOOKUP"? | Excel Worksheet Functions |