Problem with IsErr

strongc

New Member
Joined
Nov 1, 2011
Messages
3
I am writing a function and having trouble. I have stripped out all but the troublesome line


Function TestFunction (Inputstr as string)

If IsErr( Application.WorksheetFunction.Find("*", InputStr) then
TestFunction = "There was an error."
Exit Function
End If

Test fuction = "There was no Errors."

End Function


The problem comes in that the function simply throws a value error and exits the function on the if statment line instead of proceeding to the next line. I have also tried replacing IsErr with IsError, and Application.WorksheetFunction.IsErr.

Any thoughts would be appreciated.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

Drop the worksheetfunction, and just use
Application.Find.


Or even better, use the built in VBA equivilent..
Instr..

It won't return an error if the value isn't found, it just returns 0 instead..
so you don't have to test for error, just > 0
If the result of Instr is 0, the substring was not found
Otherwise, it returns the position# where the substring was found.

the syntax for instr is

Instr(Start position, String Searched, substring searched for)


Hope that helps.
 

strongc

New Member
Joined
Nov 1, 2011
Messages
3
Wow that is fast. That was a good idea. I tried:


Function TestFunction(InputStr As String)

If (InStr(0, InputStr, "*") = 0) Then
TestFunction = "entered case statements"
Exit Function
End If

TestFunction = "end of statements reached"

End Function


but no Dice. For some reason, it is still throwing a value error on the if statement.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
the minimum number for the StartPosition is 1

If (InStr(1, InputStr, "*") = 0) Then
 
Last edited:

strongc

New Member
Joined
Nov 1, 2011
Messages
3
Thank you so much. That fixes it all. I have been trying to find that error for over 3 hours. I was indeed trying to find the * character (hindsight now reveals that it is not perhaps the best sybol choice to indicate an error). I never put it together that it was the wildcard character. Again thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,758
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top