Using Worksheet Function Find in VBA

B

bcmiller

I am trying to use the worksheetfunction find to establish whether
text string contains a comma or not (refer below)

testComma = WorksheetFunction.Find(",", ResultsValue, 1)

I have also tried

testComma = iserror(WorksheetFunction.Find(",", ResultsValue, 1))

However, if the string does not contain a comma I get a run-time erro
1004

Unable to get Find propert of WorksheetFunction class.

Any tips would be appreciated
 
Y

yogendra joshi

Try this

Sub test()
If ActiveCell.Value Like "*,*" Then MsgBox "Contains "","""
End Sub
 
B

Bob Phillips

Try

On Error Resume Next
testComma = WorksheetFunction.Find(",", ResultsValue, 1)
If testComma > 0 Then
...
On Error Gotol 0

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

if instr(ResultsValue,",") > 0 then
msgbox ResultsValue & " contains a comma"
End if


Demo'd from the immediate window:

ResultsValue = "abcde,fghih"
? instr(ResultsValue,",")
6
ResultsValue = "abcdefghih"
? instr(ResultsValue,",")
0
 
B

bcmiller

Thanks All.

I have used variations of each of your code samples for differen
purposes and they work a treat.

Cheers

b
 
Top