If a range contains a number
This tutorial shows how to test if at least one cell in a range contains a number by using an Excel formula or VBA
Example: If a range contains a number
|
=IF(SUMPRODUCT(--ISNUMBER(B5:B9))>0,"Contains Number","No Number")
|
This formula uses a combination of the Excel IF, SUMPRODUCT and ISNUMBER functions to test if a selected range (B5:B9) contains a cell that only captures numbers. The SUMPRODUCT and ISNUMBER functions are used to count the number of cells in a range that contain only numbers, and then the IF function is used to return a value of "Contains Number" if the count of cells that contain numbers is greater than 0, alternatively, if there are no cells in a range that contain a number the IF function will return a value of "No Number".
|
Sub If_Range_Contains_Number()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Dim cell As Range
Dim ws As Worksheet
Dim Rng As Range
Dim cell As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B5:B9")
Set Rng = ws.Range("B5:B9")
'calculate if a range contains numeric cells
For Each cell In Rng
For Each cell In Rng
If IsNumeric(cell) = True Then
ws.Range("E4") = "Contains Number"
Exit For
Exit For
Else
ws.Range("E4") = "No Number"
End If
Next cell
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code.
Range to Test: Select the range that you want to search through for cells that contain only numbers by changing the range reference ("B5:B9") in the VBA code.
True and False Results: In this example if a range contains at least one cell that only has numbers the VBA code will return a value of "Contains Number". If a range does not contain any cells that only has numbers the VBA code will return a value of "No Number". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Worksheet Selection: Select the worksheet which captures the range that you want to test for cells that contain only numbers by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code.
Range to Test: Select the range that you want to search through for cells that contain only numbers by changing the range reference ("B5:B9") in the VBA code.
True and False Results: In this example if a range contains at least one cell that only has numbers the VBA code will return a value of "Contains Number". If a range does not contain any cells that only has numbers the VBA code will return a value of "No Number". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Worksheet Selection: Select the worksheet which captures the range that you want to test for cells that contain only numbers by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
EXPLANATION
tutorial shows how to test if a range contains a cell with a number and return a specified value if the formula tests true or false, by using an Excel formula and VBA.
This tutorial provides one Excel method that can be applied to test if a range contains at least one cell that has only numbers by using a combination of Excel IF, SUMPRODUCT and ISNUMBER functions. In this example, if the SUMPRODUCT and ISNUMBER part of the formula returns a value of greater than 0, meaning the range contains cells that only captures number, the test is TRUE and the formula will return a "Contains Number" value. Alternatively, if the SUMPRODUCT and ISNUMBER part of the formula returns a value of 0, meaning the range does not have any cells that only capture numbers, the test is FALSE and the formula will return a "No Number" value.
This tutorial provides one VBA method that can be applied to test if a range contains at least one cell that has only numbers by looping through each cell in a selected range and using the IsNumeric function to identify if a cell contains only numbers. As soon as the it identifies the first cell that contains only numbers it will return a value of "Contains Number" and exit loop. If the range does not contain any cells that contain only numbers, after looping through each cell in a range, it will return a value "No Number".
FORMULA
=IF(SUMPRODUCT(--ISNUMBER(range))>0,value_if_true, value_if_false)
=IF(SUMPRODUCT(--ISNUMBER(range))>0,value_if_true, value_if_false)
ARGUMENTS
range: The range of cells you want to test if they contain only numeric values.
value_if_true: Value to be returned if the range contains at least one cell that has only numbers.
value_if_false: Value to be returned if the range does not contain any cells that contain only numbers.
range: The range of cells you want to test if they contain only numeric values.
value_if_true: Value to be returned if the range contains at least one cell that has only numbers.
value_if_false: Value to be returned if the range does not contain any cells that contain only numbers.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a range contains text | How to test if a range contains at least one cell with only text and return a specified value using Excel and VBA | |
| If a range contains a specific value by column | How to test if a range contains a specific value by column and return a specified value using Excel and VBA methods | |
| If a range contains a value less than | How to test if a range contains a value less than a specific value and return a specified value using Excel and VBA methods | |
| If a range contains a specific value by row | How to test if a range contains a specific value by row and return a specified value using Excel and VBA methods | |
| If a cell contains text | How test if a cell contains text and return a specified value using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| ISNUMBER Function | The Excel ISNUMBER function tests a specified value (cell) if it's a numeric value and returns TRUE if it's a text value or FALSE if it's not a numeric value | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values |