If cell values are present in an array

[cfdh_edmundo]

Hi,

I have the following code below which works exactly as I want it to:

If Cells(rowCount, 1).Value = Range("D2").Value And Not IsEmpty(Cells(rowCount, 1).Value) Then
    Cells(rowCount, 14).Font.Color = vbRed
End If

But now I want to the = Range("D2").Value section.


Instead of if statement running when the value equals cell D2, I would like it to run if the value matches any element of the array feeArray().

I've already defined this feeArray() previously and all of the elements are strings.


Any help would be great!

Thanks!

 

[jmacleary]

Hi there. I think this will work (not tested):

<code>
If <code>UBound(Filter(feeArray, <code>Cells(rowCount, 1).Value</code>)) > -1</code> And Not IsEmpty(Cells(rowCount, 1).Value) Then
    Cells(rowCount, 14).Font.Color = vbRed
End If
</code>

 

[Mike Blackman]

Hi Edmundo,

You can use Application.Match to identify the item in your array:

    Dim Arr(3)
    Dim MatchValue As Long
    
    Arr(0) = "North"
    Arr(1) = "South"
    Arr(2) = "East"
    Arr(3) = "West"
    
    MatchValue = Application.Match("South", Arr, 0)

    If IsNumeric(MatchValue) Then Call MyCode

 

[offthelip]

You haven't said what dimensions feearray has so I assumed one dimension

If Not IsEmpty(Cells(RowCount, 1).Value) Then
For i = LBound(feearray) To UBound(feearray)
If Cells(RowCount, 1).Value = feearray(i) Then
    Cells(RowCount, 14).Font.Color = vbRed
    Exit For
End If
end if
Next i

Code NOT TESTED

 

[cfdh_edmundo]

perfect, thank you very much!!