Deleting #N/A from Cell Values VBA or formula help please

[longbow007]

Hello, could someone please help me with a formula for Excel 2003 to do the following:

Workbook name: C-27J Outstanding Spares Requisitions

Worksheet name: Master_List

Column: Z

Range: Z3:Z1000

I need a formula or VBA code to delete all instances of any cell value with #N/A.

Any help would be greatly appreciated.

Kind regards,

Chris

[David A Coop]

Hi Chris,

A couple of questions:
1. Does the workbook you want to scan have a formula that produces the #N/A result, or are you importing the data that way?
2. Does it matter that the cell will be blank? (You will no longer have the formula in place).

Have you considered changing the formulas which give the #N/A result?

Since you are using XL2003, you need to use an IF(ISNA...) or IF(ISERROR...) combination. This way, you can force excel to return whatever you want - usually 0 or nul.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[longbow007]

Hi David, thank you for your reply - much appreciated.

The workbook I have does not have any formulas. It does not matter that the cell will be left blank. I am trying to use a SUMPRODUCT function in the range Z3:Z1000 and it does not like any cells with #N/A in them. The SUMPRODUCT function is: =SUMPRODUCT(--($G$3:$G$1000="AOG"),--($Z$3:$Z$1000>0))

When I delete all instances of #N/A from the range Z3:Z1000, my SUMPRODUCT function gives me the correct result.

The data in column Z originally came from a VLOOKUP function from another worksheet named: Buy_Summary

The formula I use is: Range("Z3:Z10000").Formula = "=VLOOKUP(B3,Buy_Summary!$A$4:$R$10000,17,False)"

After which I change the formula result into a value by using the following VBA code:

Columns("Z:Z").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Range("A3").Select

These formulae/code are in a macro.

From my understanding of what you are saying, is that I may be able to modify my VLOOKUP formula with IF(ISNA...) or IF(ISERROR...)?

I hope this helps - if not please let me know.

Kind regards,

Chris

[David A Coop]

Hi Chris,

I think you could make the following change to the code that creates the source value in Column Z.

=IF(ISERROR(VLOOKUP(B3,Buy_Summary!$A$4:$R$10000,17,False)),"",VLOOKUP(B3,Buy_Summary!$A$4:$R$10000,17,False))

This will place a blank cell (nul from the "") where the value in B3 is not found instead of the #N/A. This should solve the problem.

Alternatively, in your macro you could add this bit of code just after you do the paste - :

Please Login or Register  to view this content.

I haven't tested it, but I think the selected range will be "held" after the PasteSpecial command.

This should replace all the #N/A's.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[longbow007]

Thanks David A Coop - greatly appreciated. I ended up doing what you suggested with the find and replace code using the Excel macro recorder. Also, thank you for the suggested:

=IF(ISERROR(VLOOKUP(B3,Buy_Summary!$A$4:$R$10000,17,False)),"",VLOOKUP(B3,Buy_Summary!$A$4:$R$10000,17,False))

formula. This will be very handy and useful.

Kind regards,

Chris