Excel VBA – If Statement Checking If Value is in Array

I have a lot of variables I have stored in an array. I am trying to use an If Then statement to see if the value of a specific cell is in that array. Here is my code:

Dim Vars1 As Variant
Vars1 = Array("Stage 2", "Stage 3", "Stage 4", "Stage 5", "Stage 6", "Stage 7", "WIP Cleanup", 
"Road Test", "Test", "Test Cleanup", "In Bay Inspection", "In Bay Clean Up", "PDI", "PDI 
Cleanup", "Verify", "Complete", "Pictures", "Remove", "ECD", "Platform Install", "#N/A")
'Cuts special delivery parts for Line 3
For RowCounter = LastRow To 1 Step -1
    If InStr(1, Range("F" & RowCounter), "underslung", vbTextCompare) Then 'Underslung
        If Range("B" & RowCounter).Value = "FA Line 3" And Range("N" & RowCounter).Value = Vars1 Then
            Rows(RowCounter).EntireRow.Cut _
                Destination:=Sheets("FA3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        End If
    End If
Next RowCounter

I don’t think the code is working because it’s not cutting and pasting the row. How do I get the if then statement to check and see if the value in "Range("N" & RowCounter)" is in the array?

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

...And Range("N" & RowCounter).Value = Vars1 Then

This part of your code, you’re comparing a single value to an array. You should check if the value is in the array. I don’t know off the top of my head if there is an Excel VBA function that does this. It’s not difficult to write a function that tests a value is in an array. Here is an idea,

Option Explicit
Option Base 0

Function InArray(ByVal pstrVal As String, ByVal pvntArray As Variant) As Boolean

    Dim lngIdx As Long

    For lngIdx = 0 To UBound(pvntArray)
        If (pstrVal = VBA.CStr(pvntArray(lngIdx))) Then
            InArray = True
            Exit Function
        End If
    Next lngIdx

    InArray = False
End Function

To test this function,

Sub Testing123()

    Dim Vars1 As Variant

    Vars1 = Array("Stage 2", "Stage 3", "Stage 4", "Stage 5", "Stage 6", "Stage 7", "WIP Cleanup", _
              "Road Test", "Test", "Test Cleanup", "In Bay Inspection", "In Bay Clean Up", "PDI", "PDI", _
              "Cleanup", "Verify", "Complete", "Pictures", "Remove", "ECD", "Platform Install", "#N/A")

    Debug.Print InArray("Testar", Vars1)
    Debug.Print InArray("Test", Vars1)
    Debug.Print InArray("In Bay Inspection", Vars1)
    Debug.Print InArray("Out Bay Inspection", Vars1)
End Sub

So where you have your condition, first line in my answer, try this;

...And InArray(Range("N" & RowCounter).Value, Vars1) Then

Hope this helps


Solution 2

Another way to get around the problem is to stop describing constants as an array. Describe it as

Const Vars1 As String = "Stage 2" & vbTab & "Stage 3" & vbTab & "Stage 4" & vbTab _
    & "Stage 5" & vbTab & "Stage 6" & vbTab & "Stage 7" & vbTab _
    & "WIP Cleanup" & vbTab & "Road Test" & vbTab & "Test" & vbTab _
    & "Test Cleanup" & vbTab & "In Bay Inspection" & vbTab & "In Bay Clean Up" & vbTab _
    & "PDI" & vbTab & "PDI Cleanup" & vbTab & "Verify" & vbTab & "Complete" & vbTab _
    & "Pictures" & vbTab & "Remove" & vbTab & "ECD" & vbTab & "Platform Install" & vbTab & "#N/A"

and use the same InStr(...) as for column F

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply