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?

2 Answers

...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

njc

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

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy