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...
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...