If a range contains a specific value
|
Macro Title:
|
|
Worksheet Variable:
|
|
Worksheet Name:
|
|
Cell Reference of Specific Value:
enter the cell reference that captures the value that you want to test if a range contains this value |
|
Test Range Reference:
enter the range that you want to test |
|
Output Cell:
|
|
Value if test is True:
enter the value that you want returned if a range contains the specified value |
|
Value if test is False:
enter the value that you want returned if a range does not contain the specified value |
|
First Comment:
' |
|
Second Comment:
' |
Sub If_a_range_contains_a_specific_value()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if any cells in the range (C8:C14) contain the value in cell (C5) and then return a specified value
If Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ws.Range("C5")) = 0 Then
ws.Range("E8") = "In Range"
Else
ws.Range("E8") = "Not in Range"
End If
End Sub
Sub If_a_range_contains_a_specific_value()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if any cells in the range (C8:C14) contain the value in cell (C5) and then return a specified value
If Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ws.Range("C5")) = 0 Then
If Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ws.Range("C5")) = 0 Then
ws.Range("E8") = "In Range"
Else
ws.Range("E8") = "Not in Range"
End If
End Sub