find position of the cell with certain contents

Hans810

Board Regular
Joined
Dec 1, 2005
Messages
62
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

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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

Board Regular
Joined
Dec 1, 2005
Messages
62
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

Board Regular
Joined
Dec 1, 2005
Messages
62
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

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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! :)
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top