How To Validating Range of Cells

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.
0
Utf
11/16/2009 5:48:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1235 Views

Similar Articles

[PageSpeed] 51

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. 


0
Tim
11/16/2009 6:56:43 AM
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. 

0
Rick
11/16/2009 7:13:25 AM
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. 
> 
> .
> 
0
Utf
11/16/2009 11:44:01 AM
Reply:

Similar Artilces:

Delete date range of emails in mailbox using ESM?
In Exchange 5.5 we could click on a mailbox and select a date range and set it to delete those emails, I can' find a way in ESM for Exchange 2003. I have tried deleting a few 1000 emails from a mailbox that stores audit emails using my Outlook and it takes forever, is there a feature from ESM that can still do this? You could setup a mailbox manager rule (via recipient policy section). Another option would be to use Exmege. Set a date range and then tell it to archive rather than just export. "SW" <SW@discussions.microsoft.com> wrote in message news:77B5A0B6-CD00-4...

paste over a filtered range
I have a spreadsheet with 40000 records, I have filtered down to 1000 records that have one coloumn of incorrect information that needs replacing, how do I paste the new data over the old?? What keeps happening is the new data pastes over the first 1000 records, not only the filtered recorsds freddie When you have your filter in place and the 1000 records are showing, hit F5>Special>Visible cell only and OK. Now paste your replacement data. Gord Dibben Excel MVP On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711" <freddie2711@discussions.microsoft.com> wrote: >...

named cell list
Could some kind person please tell me how to list the named cells of a XL 2007 spreadsheet in a worksheet. I have just upgraded from 2003 where I used the Lotus help but cant find it in 2007. many thanks. On Sat, 29 Aug 2009 11:45:22 +1000, "Keith" <oldpos@internode.on.net> wrote: >Could some kind person please tell me how to list the named cells of a XL >2007 spreadsheet in a worksheet. I have just upgraded from 2003 where I used >the Lotus help but cant find it in 2007. > >many thanks. Have a look here: http://rhftech.com/hd/excel-print-a-list-of-n...

Locking cells #3
I have a spreadsheet that i'm trying to lock in such a way that only a few cells are selectable and editable. I'm using Excel 03, and whenever I try to do it, it'll still allow me to select the cell. It won't let me edit it, but i want to set it up so that the cells are not selectable at all. Any help would be greatly appreciated! -- RKAMAN ------------------------------------------------------------------------ RKAMAN's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14605 View this thread: http://www.excelforum.com/showthread.php?threadid=2741...

create rows within an Excel cell
Can rows be used within a cell? No. You can create multiple lines in a cell by changing it's format to: Format | Cells and click on Alingment. Click in the box for wrap. Or, you can force a new line at a specific point by pressing Alt+Enter while you are typing in the cell. tj "Tony G" wrote: > Can rows be used within a cell? Hi ALT+ENTER -- Regards Frank Kabel Frankfurt, Germany "Tony G" <TonyG@discussions.microsoft.com> schrieb im Newsbeitrag news:DA13DF1C-F8C7-4260-8328-C47622E6B3B4@microsoft.com... > Can rows be used within a cell? * The for...

Make a name = column cell range in table?
I have a table with a header containing names for each column. Somehow I ended up with names defined for each column, but the range doesn't automatically expand to encompass the full column height (when I increase the rows in my table). Is there some means of configuring things so the column names appearing in my table header will automatically be defined? Check out this web page of Debra Dalgleish: http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGro...

Cell Comments #2
Hey all Is there any reason a cell comment would be restricted from editing? A comment was "inserted to a cell" but when you right click on the cell and hit edit comment the box that normally comes up that you can edit doesn't .... yet a few columns over you can do it just fine.. Any thoughts? Office 2003 Excel. Thanks. How about a wild guess??? The first comment isn't really a comment. It's an Input Message from Data|Validation. Andre wrote: > > Hey all > > Is there any reason a cell comment would be restricted from editing? > > A commen...

How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook?
Hello How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? The code is: Range("J16").Select SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$4:$I$12" SolverSolve UserFinish:=True For each ws in ActiveWorkbook.Sheets ws.Range("J16").Select SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$4:$I$12" SolverSolve UserFinish:=True Next "Dmitry" wrote: > Hello > How to repeat a code for selected sheets (or a c...

Msgbox entry validation
The following Macro will not loop more than twice the error entries. Will someone help me correct it to loop indefinely? Thanks Sub Entry_validation() Dim Teststring As String Dim Inputstring As String Cells(1, 1) = "ABC" 'as given filename Cells(2, 1) = "DEF" 'as given filename Cells(3, 1) = "GHI" 'as given filename Cells(4, 1) = "JKL" 'as given filename errorloop: Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort to abort en...

Data Validation -> Validation list is larger than the cell width
I have a workbook that I've just added data validation to one cell. FWIW, the data validation is based on a named range. Here's the (perceived) problem. When I've set up data validation in the past, the selection is the same width as the cell. In this case, the list starts almost a full cell width to the left. What am I missing? Thanks, Barb Reinhardt Barb, In Excel2003 I can replicate what you describe under the following conditions: 1. the named range contains entries that are wider than the data validation cell 2. The data validation cell is r...

Formatting copied cells
How can I format a cell to not display anything when the cell that it is copied from doesnt have any values in it? Hi Brett, I think you are are referring to a formula here? If you have the formula "=A2" in a cell, it will show the value in A2. If A2 is empty, it shows a 0, or even #N/A or something nasty like that. If you change the formula to read "=IF(A2="","",A2)", it will only show a value if there is a value in A2. I hope this is what you were after? "Bret" wrote: > How can I format a cell to not display anything when the ce...

In Excel when you click a cell in spreadsheet and move the mouse
In Microsoft Excel 2003 (Windows XP after installation of 2nd pack), when you click a cell in spreadsheet and move the mouse, multiple cells are selected. Repeatedly clicking in other cells continues to select cells instead of clearing the current selection. I have tried to use F8, but there is no EXT. Hi see: http://www.mvps.org/dmcritchie/excel/ghosting.txt -- Regards Frank Kabel Frankfurt, Germany "valanddel" <valanddel@discussions.microsoft.com> schrieb im Newsbeitrag news:34B753F5-0FB7-4643-AE1C-8D7AC01600D8@microsoft.com... > In Microsoft Excel 2003 (Windows X...

Validating against a DTD
Hello All, I am trying to validate an xml file against a DTD. I went through a lot of examples especially: http://www.xmlforasp.net/codebank/util/srcview.aspx?path=../../codebank/System_Xml/XmlValidatingReader/Validator/validator.src&file=validator.cs&font=3 but its not working in my case. First of all, when i try to add my DTD file in XmlSchemaCollection object, it throws an error saying DocType element is expected. I am using NITF DTD for this and there is no DocType element in it. Any help will be appreciated. Thanks. VD You should not add DTDs to XmlSchemaCollection. XmlSchema...

Delete rows based on a cell value in the row
I'd like to delete rows from a spreadsheet based on the value of a cell in a row. This is a very large spreadsheet (60,000+ lines). I want to delete rows based on a value (in this case all parts which are purchased- vs mfd) without destroying the spreadsheet structure. In this case I have several bills of material where I want to list all the assemblies, but not the purchased parts. It seems it should be possible, but I'm not having any luck with the filter method. Thanks, Dave sort?? or use a macro to delete, from the bottom up, if the cell contains your text -- Don Gu...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Macro to reference to a cell in indiv sheets
Hi, I have a column A that lists the name of all the sheets in my workbook. I'd like to have column B equals to cell C10 of the corresponding sheets in column A. I already have a macro to list all the sheets in my workbook so I'd like this macro to update column B at the same time I run the macro. Thanks! Val One way: Assuming your worksheet list starts in A2: You could do this without macros by entering: B2: =IF(A2<>"",INDIRECT("'" & A2 & "'!C10),"") and copying down as far as necessary. If your concern is havi...

Search a range of cells for a specific word
I want to search every cell in a column until I get to a cell that has a sentence that starts with the word "Disclaimer:" How would I write code for that? Use edit>find>find>"Disclaimer". Record a macro while doing -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Bishop" <Bishop@discussions.microsoft.com> wrote in message news:BF1DFB02-5A16-454F-A655-5B07AA69F96C@microsoft.com... >I want to search every cell in a column until I get to a cell that has a > sentence that starts with the word "Dis...

Change the number of charaters in a cell by adding a zero to the beginning
I have a column with a mix of eight and nine character lengths. I need to add a zero to the beginning of the eight character cells. I tried nine zero's in the custom format with no results. Thanks Select all the cells and Format>Cells>Custom>000000000>OK. You cannot simply "add" a zero to the left (front) of the number. Oh, I suppose you could concatenate... -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Peter" <anonymous@discussions.microsoft.com> wrote in message news:BB98AF7A-CD89-4611-8C88-07B83D99E4F5@microsoft.com... >...

Validation dropdown list is not coming visible
Hi, I'm using XP and 2003. I have a problem that I have once found resolution, but now it came again and cannot remember what needs to be done. Here is the problem: There is a column where Validation is specified in right manner with error message in case of error. When selecting the cell - dropdown list is not coming visible. In case I write something wrong it gives error message. If I recall correctly - it was something to do with some kind of list. How can I change this to work properly? I tried to paste new column from another file where the column is working as I want...

How to replace the last digit in a cell with a letter
My cells in a column are filled with amounts for which I have to change the last digit into a letter. E.g. if last digit is 1 and the amount is positive it should be changed into an A if it's negative it should be changed in a J If the last digit is 2 and the amount is positive it should be changed into a B, if it's negative it should be changed into a K. If the last digit is 3 and the amount is positive it should be changed into a C, it it's negative it should be changed into a L. And so on. I tried to use this formula but I'm hitting the 7 nested functions ceiling because I ...

reinstall-validation problem
Hardware hit from lightning so new hd and video card and start over. All goes well with install using a slipstreamed XP-Pro SP3 disk but I notice it never asks for the Product Key during install yet it Activates itself during one of the boots and I think little of it until ... During the updates which followed (100+!!!) I notice Windows Defender doesn't seem to run or leave itself installed. As it is running on my laptop figured I just download it. When I run it, it wants to first do the Genuine Advantage bs which I let it, at which time it says "bogus". Ugh, g...

excel cell borders
Have Office 2000 on PC & Office X on Mac. Excel in X has a "Draw Borders" tool & toolbar so can draw cell borders with pencil tool. Does any later PC version have same tool? The Draw Borders toolbar is available starting in Excel 2002. If you click the arrow to the right of the Borders button, it's shown at the bottom of the border palette. Gary wrote: > Have Office 2000 on PC & Office X on Mac. Excel in X has a "Draw Borders" > tool & toolbar so can draw cell borders with pencil tool. Does any later PC > version have same tool? --...

Find Merge Cells
Is there a quick way through an excel workbook that you can find all Merged Cells?? I have been using the GoTo function for blank but am wondering if there is an easier way. Thank You. Denise, here is a macro by Dave Peterson Sub Found_Merged_Cells() 'macro looks for merged cells 'By Dave Peterson Dim myCell As Range Dim resp As Long For Each myCell In ActiveSheet.UsedRange.Cells If myCell.MergeCells Then If myCell.Address = myCell.MergeArea(1).Address Then resp = MsgBox(prompt:="found: " _ ...

Validation
Julie the same can be achieve without going thru the double drop down boxes. You could do it just using the combobox alone. Thank again Please stay in the ORIGINAL thread. The archives will thank you. -- Don Guillett SalesAid Software donaldb@281.com "JLong" <anonymous@discussions.microsoft.com> wrote in message news:034101c49c3b$889b5020$a501280a@phx.gbl... > Julie the same can be achieve without going thru the > double drop down boxes. You could do it just using the > combobox alone. Thank again ...

Named Ranges #2
Is there a limit in Excel on how many named ranges a worksheet can have? There is no fixed limit to the number of names a worksheet can have. Unless you consider "available system memory" a limitation intrinsic to Excel. For more information, search for "specifications" in online help. /i. "Colleyville Alan" <aecharbonneau@nospam.comcast.net> wrote in message news:V8fub.175133$275.563028@attbi_s53... > Is there a limit in Excel on how many named ranges a worksheet can have? > > "immanuel" <ismits.no@spam.msn.com> wrote in ...