Solved

# How to find which sheet and cell has #N/A in a workbook

Posted on 2013-01-20
302 Views
Guys, how can I find which sheet has #N/A in my workbook?
compare-rows.xlsx
0
Question by:rws1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3
• 2
• +1

LVL 50

Expert Comment

ID: 38799691
Hello,

you sheets are called Sheet2 and Sheet5. Assuming that you mean Sheet2 when you say Sheet1 and you mean Sheet5 when you say Sheet2, then put this formula in Sheet2, cell E6 and copy down

=INDEX(Sheet5!\$D:\$D,MATCH(C6,Sheet5!\$D:\$D,0))

cheers, teylyn
0

Author Comment

ID: 38799796
Thank you teylyn, for your response, however what I need is a pop-up or something to alert me where I have the #N/A in my workbook and direct me to the correct cell. I get a notice that I have a #N/A in my sheet totals, but then I have to research the whole workbook to try to find which cell is producing the problems so I can fix it.

Any thoughts in this direction would be greatly appreciated.

Once again thank you for your input.
0

Author Comment

ID: 38799802
My note on my attachment is wrong it shoud say sheet 2 and sheet 5, I don't know how to remove and replace the file.
0

LVL 10

Expert Comment

ID: 38799807
Delete row 5 from the worksheet you posted and then run try this code:
``````Sub CompareCols()
Dim v As String

v = DoCompare(Range("Sheet2!C5").CurrentRegion, Range("Sheet5!D6").CurrentRegion)
If v <> "" Then MsgBox "Missing value(s): " & v
End Sub

Function DoCompare(rng1 As Range, rng2 As Range) As String
Dim i1 As Long
Dim i2 As Long

DoCompare = ""
i2 = 1
For i1 = 1 To rng1.Rows.Count
If rng2.Cells(i1, 1).Text = "#N/A" Then
DoCompare = DoCompare & IIf(DoCompare = "", "", ",") & rng1.Cells(i1, 1).Value
End If
Next i1
End Function
``````
0

LVL 43

Expert Comment

ID: 38799817
This ARRAY formula will give you the row number of the first #NA cell in the list

=MIN(IF(ISNA(D:D),ROW(D:D)))
0

Author Comment

ID: 38799916
Thank you to both you guys, however I am having no success getting either of your suggestions to work, would you mind to put it in the spreadsheet and send it back?
Thank you,
Rob
0

LVL 43

Expert Comment

ID: 38801064
The formula I gave is in column E which gives only the first occurence of the the #NA

In column F I have given another formula which when copied down will show further occurences,
0

Author Comment

ID: 38801543
ssaqibh, thank you for your response, I only see the formula for column D, above
=MIN(IF(ISNA(D:D),ROW(D:D)))

I am confused as to your direction

Robert
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 250 total points
ID: 38801611
Copy-of-compare-rows.xlsx
0

LVL 10

Accepted Solution

tdlewis earned 250 total points
ID: 38801796
Here is the sheet with the macro I wrote to find all instances of #N/A.
compare-rows.xlsm
0

Author Comment

ID: 38803519
Thank you, your illustrations are great.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take â€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll