Using Worksheet Function Find in VBA

[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

 

[yogendra joshi]

Try this

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

 

[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)

 

[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

 

[bcmiller]

Thanks All.

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

Cheers

b