主页 / computer / 问题 / 1701402
Accepted
wyatt7613
Asked: 2022-01-26 21:12:23 +0800 CST

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

  • 772

我有很多变量存储在一个数组中。我正在尝试使用 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)”中的值是否在数组中？

microsoft-excel microsoft-excel-2010

2 个回答

  1. Best Answer
    ...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

    希望这可以帮助

    新泽西州

    • 1

  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 列相同的方法

    • 0

相关问题