If a range contains a specific value by column
|
Macro Title:
|
|
Worksheet Variable:
|
|
Worksheet Name:
|
|
Formula Variable:
|
|
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 does not contain a specified value |
|
Value if test is False:
enter the value that you want returned if a range contains a specified value |
|
First Comment:
' |
|
Second Comment:
' |
Sub If_a_range_contains_a_specific_value_by_column()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a range contains a specific value by column and then return a specified value
Result = Application.HLookup(ws.Range("C5"), ws.Range("C8:G8"), 1, False)
If IsError(Result) Then
ws.Range("I8") = "No"
Else
ws.Range("I8") = "Yes"
End If
End Sub
Sub If_a_range_contains_a_specific_value_by_column()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a range contains a specific value by column and then return a specified value
Result = Application.HLookup(ws.Range("C5"), ws.Range("C8:G8"), 1, False)
If IsError(Result) Then
Result = Application.HLookup(ws.Range("C5"), ws.Range("C8:G8"), 1, False)
If IsError(Result) Then
ws.Range("I8") = "No"
Else
ws.Range("I8") = "Yes"
End If
End Sub