find position of the cell with certain contents

[Hans810]

Good evening everybody,

With a for...next loop I check the cells in the range A1...AU3000 for the error content #Value!.
If the error content has been located, I want to have the feedback of the cell (eg. Z56) which was containing the error message.
Any help is very appreciated.
With regards from Veenendaal, The Netherlands.

 

[Chris Macro]

Does this work for you?

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A1:AU3000")<br>  <SPAN style="color:#00007F">If</SPAN> cell.Value = "#Value!" <SPAN style="color:#00007F">Then</SPAN><br>    Debug.Print cell.Address <SPAN style="color:#007F00">'ctrl + g to see outputs</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Hans810]

Hi Chris,

THX it works.
What if I want to have these cell coordinates in a worksheet?
How do I come from your Debug.Print cell.Address to a Range("A10") being filled with the coordinates?

By the way, I used: If cell.Text = "#Value!" Then...

Regards,

Hans

 

[Hans810]

Found the solution (with the little help of a friend....Chris):

Sub test()
Dim cell As Range
Row = 10
For Each cell In Range("A318:AU318")
If cell.Text = "#VALUE!" Then
Debug.Print cell.Address 'ctrl + g to see outputs
Sheets("SETTINGS").Select
Range("A" & Row) = cell.Address
Row=Row+1
Sheets("DATA").Select

End If

Next cell

End Sub

 

[Chris Macro]

I would recommend staying away from selecting tabs and just directly state which worksheet you want to target. Here's how I would would write your solution:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br>Row = 10<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A318:AU318")<br>  <SPAN style="color:#00007F">If</SPAN> cell.Text = "#VALUE!" <SPAN style="color:#00007F">Then</SPAN><br>    Debug.Print cell.Address <SPAN style="color:#007F00">'ctrl + g to see outputs</SPAN><br>    <br>    Sheets("SETTINGS").Range("A" & Row) = cell.Address<br>    Row = Row + 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Glad I was able to help you figure out your problem!