Hi all.
Maybe smb could make an advice how to do this:

I have to find all the rows in a Excel worksheet, which contain cell with a
11 digit numeric value.

E.g. two rows each with 3 cells with values:
------------------------------------
Will | Smith | 4524574561

Gerry | Smith | LowStreet 20
------------------------------------


So, the first row should be accepted, and the second - ignored.

I tried different examples using oSheet.Cells.Find(What:=...
but unsuccesfully.

Is there a way to use regular expressions

Thanks in advance.

I don't think you can use Find to find the cells and Find doesn't support
Regular Expressions. Try code like

Function GetValues(InRange As Range, Length As Long) As Range
Dim Res As Range
Dim R As Range

For Each R In InRange.Cells
If IsNumeric(R.Text) = True Then
If Len(R.Text) = Length Then
If Res Is Nothing Then
Set Res = R
Else
Set Res = Application.Union(Res, R)
End If
End If
End If
Next R

Set GetValues = Res
End Function

You can then call this function with code like:

Sub AAA()
Dim R As Range
Dim RR As Range
Set RR = GetValues(InRange:=Range("A1:A10"), Length:=11)
If RR Is Nothing Then
Debug.Print "NOT FOUND"
Else
For Each R In RR
Debug.Print R.Address, R.Text
Next R
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Andrius B." wrote in message
...
Hi all.
Maybe smb could make an advice how to do this:

I have to find all the rows in a Excel worksheet, which contain cell with
a 11 digit numeric value.

E.g. two rows each with 3 cells with values:
------------------------------------
Will | Smith | 4524574561

Gerry | Smith | LowStreet 20
------------------------------------


So, the first row should be accepted, and the second - ignored.

I tried different examples using oSheet.Cells.Find(What:=...
but unsuccesfully.

Is there a way to use regular expressions

Thanks in advance.

On 23 Dec., 18:42, "Andrius B." wrote:
Hi all.
Maybe smb could make an advice how to do this:

I have to find all the rows in a Excel worksheet, which contain cell with a
11 digit numeric value.

E.g. two rows each with 3 cells with values:
------------------------------------
Will * *| *Smith *| * 4524574561

Gerry | *Smith *| * *LowStreet 20
------------------------------------

So, the first row should be accepted, and the second - ignored.

I tried different examples using oSheet.Cells.Find(What:=...
but unsuccesfully.

Is there a way to use regular expressions

Thanks in advance.

Hi Andrius

The number in your example has only 10 digits, so that is what I test
for in this macro.

All row numbers that are allowed are placed in DestArray() as I don´t
know what you want do with the result.

Option Base 1
Dim DestArray() As String
Dim tRange As Range
Dim Counter As Integer

Sub Find_Cells()

Set tRange = Selection
Counter = 1
For Each c In tRange
If Len(c.Value) = 10 And IsNumeric(c.Value) = True Then
ReDim Preserve DestArray(Counter)
DestArray(Counter) = c.Row
Counter = Counter + 1
End If
Next
End Sub

Regards

Per

If Len(c.Value) = 10 And IsNumeric(c.Value) = True Then

The above statement is from your posted code. The IsNumeric function is not
one of the best ways to "proof" an entry for being all digits (or even a
floating point value for that matter). For example, put "$(2,,e12)$"
(without the quote marks) in one of the cells being tested by your code and
the logical statement for the If function will return True when it gets to
that cell. I would perform the test this way...

If c.Value Like "##########" Then

where there are as many # symbols as required digits in the entry (10 for
the example you used).

Below is something I have posted previously over in the compiled VB
newsgroups, but everything discussed in it applies to VBA world as well....

I usually try and steer people away from using IsNumeric to "proof"
supposedly numeric text. Consider this (also see note below):

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Most people would not expect THAT to return True. IsNumeric has some "flaws"
in what it considers a proper number and what most programmers are looking
for.

I had a short tip published by Pinnacle Publishing in their Visual Basic
Developer magazine that covered some of these flaws. Originally, the tip was
free to view but is now viewable only by subscribers.. Basically, it said
that IsNumeric returned True for things like -- currency symbols being
located in front or in back of the number as shown in my example (also
applies to plus, minus and blanks too); numbers surrounded by parentheses as
shown in my example (some people use these to mark negative numbers);
numbers containing any number of commas before a decimal point as shown in
my example; numbers in scientific notation (a number followed by an upper or
lower case "D" or "E", followed by a number equal to or less than 305 -- the
maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for
Hexadecimal, &O or just & in front of the number for Octal).

NOTE:
======
In the above example and in the referenced tip, I refer to $ signs and
commas and dots -- these were meant to refer to your currency, thousands
separator and decimal point symbols as defined in your local settings --
substitute your local regional symbols for these if appropriate.

As for your question about checking numbers, here are two functions that I
have posted in the past for similar questions..... one is for digits only
and the other is for "regular" numbers:

Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) 0 And _
Not Value Like "*[!0-9]*"
End Function

Function IsNumber(ByVal Value As String) As Boolean
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) 0 And Value < "." And _
Value < vbNullString
End Function

Here are revisions to the above functions that deal with the local settings
for decimal points (and thousand's separators) that are different than used
in the US (this code works in the US too, of course).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) 0 And Value < DP And _
Value < vbNullString
End Function

I'm not as concerned by the rejection of entries that include one or more
thousand's separators, but we can handle this if we don't insist on the
thousand's separator being located in the correct positions (in other words,
we'll allow the user to include them for their own purposes... we'll just
tolerate their presence).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
Dim TS As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Get local setting for thousand's separator
' and eliminate them. Remove the next two lines
' if you don't want your users being able to
' type in the thousands separator at all.
TS = Mid$(Format$(1000, "#,###"), 2, 1)
Value = Replace$(Value, TS, "")
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) 0 And Value < DP And _
Value < vbNullString
End Function

Rick

Good points Rick..interesting walkthrough.

It seems safer to use Excel's IsNumber function
(Excel.WorksheetFunction.IsNumber) or even SpecialCells than IsNumeric. I
haven't played around with IsNumber it enough to know if it let's
non-numeric characters "pass the test" like IsNumeric does, but it certainly
seems to evaluate better what it's function name suggests. Perhaps
"IsNumeric" should be renamed "CanBeNumeric".

--
Tim Zych
SF, CA

"Rick Rothstein (MVP - VB)" wrote in
message ...
If Len(c.Value) = 10 And IsNumeric(c.Value) = True Then

The above statement is from your posted code. The IsNumeric function is
not one of the best ways to "proof" an entry for being all digits (or even
a floating point value for that matter). For example, put "$(2,,e12)$"
(without the quote marks) in one of the cells being tested by your code
and the logical statement for the If function will return True when it
gets to that cell. I would perform the test this way...

Thanks for all the code.

I'll use it.

Andrius

I'm not sure checking the numeracy of an entry is as critical at the
spreadsheet level as it is a the programming level. If you enter a
non-number in a cell, any formulas relying on the entry to be a number will
provide instant feedback to the user so that he/she will know that something
must be corrected. However, within a program, the reaction to a bad entry is
usually hidden from the user and so the program must implement code to guard
against bad entries. Unfortunately, IsNumeric is badly named and many, many
people looking for a "normal" shaped number use it because the name seems to
indicate that is what it exists for. You are right, IsNumeric is really a
CanBeNumeric function and pretty much exists for use in the Cxxx function
(CInt, CLng, CDbl, etc.). I can just hear you saying, "Wait a minute, what
do you mean by that statement?" Well, believe it or not, VB/VBA is perfectly
happy with something like CDbl("$(2,,e12)$"); for example

MsgBox CDbl("$(2,,e12)$")

will happily display a MessageBox with -2000000000000 in it (the $ signs and
commas are flushed, the parentheses are interpreted as negating the value
and the 'e' is the normal way to show a power of 10). If the entry were
smaller, such as like "$(2,,e2)$", both CInt and CLng would be happy with it
too.

Rick


"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
Good points Rick..interesting walkthrough.

It seems safer to use Excel's IsNumber function
(Excel.WorksheetFunction.IsNumber) or even SpecialCells than IsNumeric. I
haven't played around with IsNumber it enough to know if it let's
non-numeric characters "pass the test" like IsNumeric does, but it
certainly seems to evaluate better what it's function name suggests.
Perhaps "IsNumeric" should be renamed "CanBeNumeric".

--
Tim Zych
SF, CA

"Rick Rothstein (MVP - VB)" wrote in
message ...
If Len(c.Value) = 10 And IsNumeric(c.Value) = True Then

The above statement is from your posted code. The IsNumeric function is
not one of the best ways to "proof" an entry for being all digits (or
even a floating point value for that matter). For example, put
"$(2,,e12)$" (without the quote marks) in one of the cells being tested
by your code and the logical statement for the If function will return
True when it gets to that cell. I would perform the test this way...