Hi, without going cell-by-cell, is there any quick way to validate a range of 
cells (e.g. A1:B10) to be numeric and returns the error cell address if not 
numeric? Thanks.

What's wrong with going cell-by-cell ?

Tim

"aushknotes" <aushknotes@discussions.microsoft.com> wrote in message 
news:BB6EF563-4364-4D16-A8E5-E30B917EA219@microsoft.com...
> Hi, without going cell-by-cell, is there any quick way to validate a range 
> of
> cells (e.g. A1:B10) to be numeric and returns the error cell address if 
> not
> numeric? Thanks. 

As long as the code will not be used in anyway as part of a User Defined 
Function, the you can use code constructed like this...

Dim R As Range, TextAddress As String
.....
.....
Set R = Range("A1:H14")
On Error Resume Next
TextAddress = R.SpecialCells(xlCellTypeConstants, xlTextValues).Address
If Len(TextAddress) = 0 Then
     MsgBox "All cells in range are either numbers or blanks"
Else
     MsgBox "Cell " & TextAddress & " is not a number"
End If

-- 
Rick (MVP - Excel)


"aushknotes" <aushknotes@discussions.microsoft.com> wrote in message 
news:BB6EF563-4364-4D16-A8E5-E30B917EA219@microsoft.com...
> Hi, without going cell-by-cell, is there any quick way to validate a range 
> of
> cells (e.g. A1:B10) to be numeric and returns the error cell address if 
> not
> numeric? Thanks. 

Perfect & very neat! Thanks a million!



"Rick Rothstein" wrote:

> As long as the code will not be used in anyway as part of a User Defined 
> Function, the you can use code constructed like this...
> 
> Dim R As Range, TextAddress As String
> .....
> .....
> Set R = Range("A1:H14")
> On Error Resume Next
> TextAddress = R.SpecialCells(xlCellTypeConstants, xlTextValues).Address
> If Len(TextAddress) = 0 Then
>      MsgBox "All cells in range are either numbers or blanks"
> Else
>      MsgBox "Cell " & TextAddress & " is not a number"
> End If
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "aushknotes" <aushknotes@discussions.microsoft.com> wrote in message 
> news:BB6EF563-4364-4D16-A8E5-E30B917EA219@microsoft.com...
> > Hi, without going cell-by-cell, is there any quick way to validate a range 
> > of
> > cells (e.g. A1:B10) to be numeric and returns the error cell address if 
> > not
> > numeric? Thanks. 
> 
> .
>