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

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.

Check the help file for "Instr" which works like "Find" in a normal formula
(but with the terms reversed, IIRC).

(aircode)
not(iserror((Instr(c.Value,".")))

then the wraparound is because if it doesn't find a period it returns an
error (otherwise it returns a position) so the not/iserror gives you a "true"
if a period is found.

HTH
Keith

"Jonathan Brown" wrote:

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

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.

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



Jonathan Brown wrote:

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

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

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


On 6 Feb., 02:09, Jonathan Brown
wrote:
I have a User Defined function that counts the cells in a range that meet a
certain criteria. *The criteria a

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.

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

--
Rick (MVP - Excel)


"Jonathan Brown" wrote in message
...
I have a User Defined function that counts the cells in a range that meet a
certain criteria. The criteria a

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.