Return first found error adress in range A1 to D500?

[Polymorpher]

I've used some search and some index +- match. But getting this work is beyond me.

If i have errors in A22, B13 and D1 i need to be able to find them via 1 formula (i know i can only track one at a time with this approach) - my document requires me to fix them so getting 1 address, resolving it and moving to the next one is good enough.

Otherwise i have to dedicate a search formula to each column, and i got like 200 of em.

Epic solve by Tony Valko below:

Search tags:
Find results in array.
Find error in array.
Find result in multiple rows and columns.
Search address in array.

[mangesh.mehendale]

Hiii please find attached sheet...

It is not final solution... but check it..

In following code error handler is pending and also #N/A error yet to cover

Sub celladrss()
    Dim x As String
    Range("A1").Select
    With Cells
        .Find(What:="#VALUE!", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        x = ActiveCell.Address
    End With
    Sheets("First found").Select
    Range("A1").Value = x
End Sub

[Polymorpher]

Yeah, I'm waiting for the pros to give their judgment if it could be done without VBA.
Otherwise formulating it isn't hard since you are currently only checking for #VALUE!. substituting the explicit text search with IF(ISERROR(true)) should cover it.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	#N/A	99	31	#VALUE!		A1
2	53	#NULL!	49	25		B2
3	6	10	#NAME?	44		C3
4	90	92	47	#NUM!		D1
5	60	20	75	32		D4
6	------	------	------	------	------	------

This array formula** entered in F1:

=IFERROR(ADDRESS(MOD(SMALL(IF(ISERROR($A$1:$D$5),COLUMN($A$1:$D$5)*10000+ROW($A$1:$D$5)),ROWS(F$1:F1)),10000),TRUNC(SMALL(IF(ISERROR($A$1:$D$5),COLUMN($A$1:$D$5)*10000+ROW($A$1:$D$5)),ROWS(F$1:F1))/10000),4),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[mangesh.mehendale]

okay... but If you dont want a Macro then I think it is possible with User defined function(i.e. UDF) using loops..

[Polymorpher]

Hoooly ****... No wonder I couldn't do it - its looks like an excerpt from the third tome "Mysteries of the Excel Grandmasters".

Yeah it works. And there is handy way of using it. Just bind the result to a HYPERLINK function and you can jump to each next on demand. (Especially if you have a frozen view top row with such navigation gimmicks)

Also I suggest replacing the end "") with "some text") so the formula doesn't get lost from view when you don't have errors.

......
And for the others uninitiated like me:

Use his wizardry somewhere in a cell say A1, then point a =HYPERLINK(CONCATENATE("#",A1),"Name of how you see the link")
I refrain from nesting stuff in hyperlinks cuz its weird sometimes.

the # basically tells the hyperlink to link in the document and sheet its in (i think), because it doesn't work if you drop the # and just tell it to go to "A1".

In case you don't know how to enter the hyperlink functions either navigate to it and F2 or hold the leftclick longer so you stay in position instead of getting redirected.

[Tony Valko]

Thanks for the rep and feedback!