UDF that checks if a cell contains a period.

[Jonathan Brown]

I have a User Defined function that counts the cells in a range that meet a
certain criteria. The criteria are:

1. does not contain a formula
2. is not a number
3. is not blank
4. contains a period.

I can get it to figure out the first three criteria, but I can't figure out
the expression to check to see if the cell contains a period.

Here's what my code looks like:

Function Jonathan(rng As Range) As Long

Dim c As Range

For Each c In rng

If c.HasFormula = False And c.Value <> "" And Not IsNumeric(c.Value)
And _
c.Value = "*.*" Then

Jonathan = Jonathan + 1

End If

Next

End Function

It seems to be taking the c.value = "*.*" as a string literal and there
doesn't seem to be a VBA equivalent to the .Net version of the .contains()
method. Any ideas would be greatly appreciated.

Thanks.

 

[Dave Peterson]

VBA has its own instr function:
if instr(1,c.value, ".", vbtextcompare) > 0 then
'it has a dot
...

Or you could use Like:
if c.value like "*.*" then

(I'd use instr.)

 

[Per Jessen]

Hi

This should do it:

If c.HasFormula = False And c.Value <> "" And Not IsNumeric(c.Value)
And _
InStr(1, c.Value, ".") Then

Regards,
Per

 

[Rick Rothstein]

The only thing I would add to the other posts is the you do NOT have to
check if the cell is empty... you are requiring the cell's text to have a
period; so if the cell's text has that character in it, then, by definition,
it can't be empty (so the test is not needed).