Need to see if a value of a cell exists in an array

Question

Using VBA in Excel to see if a cell contains a value that is stored in my array, and is not working. I am receiving a type mismatch error.

Sub ExecuteScript_Click()
   Dim vals As String
   vals = Array("5", "9", "12")

   Dim LastRow As Long 
   Dim i As Long
   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   For i = 2 To LastRow
      If Range("A" & i).Value = vals Then
        Range("B" & i).Value = "Value Exists"
      End If
   Next i
End Sub

You are trying to compare a **single value** to an **array**. You need either an inner loop or a `Filter()`. — Gary's Student, Oct 06 '17 at 11:34
You are comparing a cell-value with the hole array - this throws an error. Either loop over all the entries in the array, write a function to do the check (also by looping) or use a `Dictionary` instead of the array — FunThomas, Oct 06 '17 at 11:36
You also can't declare a simple variable as a String and then assign an array to it. `vals` should be a variant and then you can loop through it. — Rory, Oct 06 '17 at 11:47

Abhinav Rawat · Accepted Answer · 2017-10-09T14:38:41.843

TRy-

Sub foo()
Dim vals As Variant

 vals = Array("5", "9", "12", "-1")
Dim LastRow As Integer

Dim i As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row



For i = 2 To LastRow
      If IsInArray(ActiveSheet.Range("A" & i).Value, vals) Then
        ActiveSheet.Range("B" & i).Value = "Value Exists"


    End If
Next i

End Sub


Function IsInArray(key As Variant, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, key)) > -1)
End Function

Ref. HERE

UPDATED

Sub foo()
Dim vals As Variant

vals = Array("5", "9", "12", "-1")
Dim LastRow As Integer

Dim i As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row



For i = 2 To LastRow
    If Not IsEmpty(ActiveSheet.Range("A" & i)) Then
        If IsInArray(ActiveSheet.Range("A" & i).Value, vals) Then
            ActiveSheet.Range("B" & i).Value = "Value Exists"
        End If
    End If
Next i

End Sub


Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Thank you for this information. I am not familiar with VBA or VB and needed this jump. I think I will look for some 101 videos this weekend, but I was stuck now - Thanks! — Sean, Oct 06 '17 at 11:55
The above code will also mark empty values as existing; you can avoid that by defining a condition, e.g. `If Len(ActiveSheet.Range("A" & i).Value) > 0 Then` before writing "Value Exists" into your cell in column B. — T.M., Oct 06 '17 at 18:03

Need to see if a value of a cell exists in an array

1 Answers1