1. 

   Martin
   Member

   hi All,

   I've written this User Defined Formula, to extract a string from within another, delimited by underscores, e.g. AB_CDE-1234-56-7_89_FGH, and the result is CDE-1234-56-7.

   here's the code (it's in Spanish, but it makes no difference)

   Function extraecontrato(celda As range)

   Dim InicioCadena, FinCadena As Double, Cadena As String

   InicioCadena = application.WorksheetFunction.Find("_", celda.Value, 1) + 1
   FinCadena = application.WorksheetFunction.Find("_", celda.Value, InicioCadena)
   Cadena = Mid(celda.Value, InicioCadena, FinCadena - InicioCadena)
   If IsError(InicioCadena) Then Cadena = celda.Value

   extraecontrato = Cadena

   End Function

   My problem is that when there is no underscore, that is, when an error is returned, the IF sentence does not work, and as a result, I get a #VALUE on my spreadsheet.

   any suggestions?

   Thanks !!!

   P.S: I've tried to debug the UDF with breakpoints and F8, but it doesn't seem to work...

   Posted 3 months ago #
2. 

   Chandoo
   Key Master

   @Martin..

   You can add "on error resume next" line to your code or even write "on error goto" to handle such errors. Just google for these to learn more about these or read datapigs http://datapigtechnologies.com/blog/index.php/are-goto-statements-still-dirty/

   Posted 3 months ago #
3. 

   JP
   Member

   If there's no underscore, then InicioCadena will equal 1. To be sure, change the UDF so it starts out at zero and only add one to it if it isn't equal to zero. Otherwise, exit with a simple error message.

   Function extraecontrato(celda As range)

   Dim InicioCadena, FinCadena As Double, Cadena As String

   InicioCadena = application.WorksheetFunction.Find("_", celda.Value, 1)

   If InicioCadena > 0 Then
   InicioCadena = InicioCadena + 1
   Else
   extraecontrato = "Error"
   Exit Function
   End If

   FinCadena = application.WorksheetFunction.Find("_", celda.Value, InicioCadena)
   Cadena = Mid(celda.Value, InicioCadena, FinCadena - InicioCadena)

   If IsError(InicioCadena) Then Cadena = celda.Value

   extraecontrato = Cadena

   End Function

   Posted 3 months ago #
4. 

   Martin
   Member

   @Chandoo: I've read the post, and found nothing that I can use.

   @JP: thanks for the code !. IT works the same, and still cannot debug line-by-line the code when error. And the "error" sentence does not appear.

   For instance, I have "Client1" as a value in a cell, and when applying the UDF, returns a #VALUE error. What I want is that, in case of an error, just return the whole .value of the target cell.

   Thanks !

   Posted 3 months ago #
5. 

   Hui
   Excel Ninja

   Martin

   Try using the following which uses "InStr" instead of application.WorksheetFunction.Find

   ===========

   Function extraecontrato(celda As Range)

   Dim InicioCadena, FinCadena As Double, Cadena As String

   InicioCadena = InStr(1, celda.Value, "_")

   If InicioCadena > 0 Then
   InicioCadena = InicioCadena + 1
   Else
   extraecontrato = "Error"
   Exit Function
   End If

   FinCadena = InStr(InicioCadena, celda.Value, "_")

   Cadena = Mid(celda.Value, InicioCadena, FinCadena - InicioCadena)

   If IsError(InicioCadena) Then Cadena = celda.Value

   extraecontrato = Cadena

   End Function

   Posted 3 months ago #
6. 

   JP
   Member

   You can step through this function. Just set a breakpoint on the first line by clicking anywhere in the first line and pressing F9. Then enter the function in a cell. You should immediately jump to that line. Then press F8 repeatedly to step through the function and see where it errors.

   You might also have to declare celda As String in order for the function to work properly.

   Posted 3 months ago #
7. 

   Martin
   Member

   @Hui: Thanks, worked as a charm !!!

   I've done a small addition after the calc of FinCadena, as I still got a #VALUE error, for those cells where there was only a single "_":

   If FinCadena = 0 Then FinCadena = Len(celda.Value)

   In that way, I always get a positive length of the string.

   @JP: don't know why, but this time the breakpoint did work. Thanks !

   Posted 3 months ago #

RSS feed for this topic

Reply

You must log in to post.