☰

Excel VBA - If 语句检查值是否在数组中

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
Excel VBA - If 语句检查值是否在数组中

我有很多变量存储在一个数组中。我尝试使用 If Then 语句来查看特定单元格的值是否在该数组中。这是我的代码：

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

我认为代码不起作用，因为它没有剪切和粘贴行。如何获取 if then 语句来检查“Range("N" & RowCounter)”中的值是否在数组中？

答案1

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

代码的这一部分，您正在将单个值与数组进行比较。您应该检查该值是否在数组中。我不知道是否有 Excel VBA 函数可以执行此操作。编写一个测试值是否在数组中的函数并不困难。这里有一个想法，

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

要测试此功能，

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

因此，如果您有这样的情况，请尝试我答案的第一行；

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

希望这可以帮助

新泽西州

答案2

解决这个问题的另一种方法是停止将常量描述为数组。将其描述为

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"

并使用InStr(...)与 F 列相同的方法

相关内容