#VALUE! in VBA giving me an error..

[rlegge]

Hi, Hopefully a simple one.. I'm struggling to handle #VALUE! in VBA.

I've a simple bit of code, and am using the worksheet FIND formula.. However it won't handle the result coming back if FIND doesn't find a string..

here is the example:

Function CleanTxt(myText As String) As String

If IsError(WorksheetFunction.Find("OLD", myText)) Then

CleanTxt = myText

Else

x = WorksheetFunction.Find("OLD", myText)
CleanTxt = WorksheetFunction.Replace(myText, x, 3, "NEW")

End If

End Function

In my spreadsheet I call the formula...=CleanTxt(C3) (C3 contains the old text and the formula is in it..)

and the result is:

thisisOLDtext thisisNEWtext
thisisnt #VALUE!

Although I just want it to gracefully exit, no matter what I try I cannot get around the #VALUE turning up in the field where it doesn't find the matching text.. (and telling me that the data is of the wrong datatype.

I know there are alternatives to this particular find / replace, but I'm trying to get to grips with using Excel Formulas, and actually trying to work out why I cannot handle the error correctly.

Many Thanks
Rich

 

[Comfy]

Add this to the Top of your Function:

On Error Resume Next

See if that works.

 

[Norie]

Rich

If you want to use formulas why not do this using a worksheet formula?

Then you can use the worksheet function ISERROR.

Or if you are replacing text you could look at REPLACE or SUBSTITUTE.

If you do want to do this in VBA try the InStr function instead of WorksheetFunction.Find.

It'll return 0 if it doesn't find anything, not an error.

 

[rlegge]

Thank you.. Its as simple as that. By the way, My wife wants to convert a lot of nested Formulas to simple to read and execute VBA Scripts, so needs to be able to handle #Errors.. rather than converting all the formula to VBA equivalents..

Cheers
Rich

 

[Comfy]

Take a look here:

http://www.cpearson.com/excel/errorhandling.htm

It may or may not help

 

[Norie]

Rich

Using On Error Resume Next isn't always the best idea.

It doesn't really stop errors happening, they still happen.

It can also cause errors to be ignored.