Find () method in VB

[fulltime]

I am using find method to look for a certain string within a cell. However, if that certain string does not exist, it will generate an error. I understand tat if the string is not found, excel will return a #VALUE, my qn is how can i turn off this error msg?

FYI, this is how i called the find method in VB, anything wrong w that?

While Application.WorksheetFunction.Find("\x", Cells(3, yLoop).Value) <> 0

I m trying to stop the macro once the string "\x" is not found by using the while loop, but is not helping..

 

[Rod D]

Hi Fulltime

I would not recommend turning off the error messages as they are global and you will not trap valid errors elsewhere in the workbook.

For the line of code -
Syntactically it is OK, but...
You use .Value to get the contents of the cell, this is not required and should be deleted.

Without seeing all of the procedure, it is difficult to see why you have used a WHILE loop - the loop will end at the first cell where the string is not found - is that correct? I am assuming you are looping through a range of cells - if not, I would use an IF construct instead to test the contents of the cell.

You don't say if you are getting an error message from this code.
In the case where the string is not found, an Error 1004 would be triggered - "Unable to get the Find property of the WorksheetFunction Class". This basically means - "String not found". If you have an error handler, you may not see it. I would use the "On Error Goto ..." trapping to cope with the case where the string isn't found. That way you can put either a Null or "Not found" into the cell and avoid the error messages being displayed.


Some other ideas...
There is also the "Find Method" in VB which works in a similar way but does not return a #VALUE! result if the string is not found. It may be worth looking it up in the VB Help.

IF you wanted to implement this in a cell, rather than within a VB procedure, the formula would be along the lines :

=IF(ISERROR(FIND("\x",<CellAddress>)),"Not found",FIND("\x",<CellAddress>))

HTH
Rod

 

[fulltime]

Hi Rod D,

u r rite, i am trying to loop through a range of cells, tats y i am using the while loop..

i will go try n see if by adding in the onErrorGoto wil work, i think it will.. thks for ur suggestion.. u been of great help