Using Worksheet Function Find in VBA
 

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

--
Message posted from http://www.ExcelForum.com

Using Worksheet Function Find in VBA
 

Try this

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


bcmiller < wrote:

I am trying to use the worksheetfunction find to establish whether a
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 error
1004

Unable to get Find propert of WorksheetFunction class.

Any tips would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Using Worksheet Function Find in VBA
 

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)

"bcmiller " wrote in message
...
I am trying to use the worksheetfunction find to establish whether a
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 error
1004

Unable to get Find propert of WorksheetFunction class.

Any tips would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Using Worksheet Function Find in VBA
 

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
--
Regards,
Tom Ogilvy

"bcmiller " wrote in message
...
I am trying to use the worksheetfunction find to establish whether a
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 error
1004

Unable to get Find propert of WorksheetFunction class.

Any tips would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Using Worksheet Function Find in VBA
 

Thanks All.

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

Cheers

b

--
Message posted from http://www.ExcelForum.com