rws1
asked on
How to find which sheet and cell has #N/A in a workbook
Guys, how can I find which sheet has #N/A in my workbook?
compare-rows.xlsx
compare-rows.xlsx
ASKER
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.
Any thoughts in this direction would be greatly appreciated.
Once again thank you for your input.
ASKER
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.
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
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) ))
=MIN(IF(ISNA(D:D),ROW(D:D)
ASKER
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
Thank you,
Rob
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,
In column F I have given another formula which when copied down will show further occurences,
ASKER
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
Thank you for your help,
Robert
=MIN(IF(ISNA(D:D),ROW(D:D)
I am confused as to your direction
Thank you for your help,
Robert
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, your illustrations are great.
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(
cheers, teylyn