而不是键入一堆“或"，语句，如何在此代码中实现功能?

问题描述

Sub test()

Dim DataRange As Range
Dim LastRow As Integer
Dim i As Integer
Dim SplitVal() As String
Dim OutputOffset As Long
OutputOffset = 0

LastRow = Cells(Rows.Count, "J").End(xlUp).Row

For i = 2 To LastRow
    If InStr(1, Cells(i, 10).Value, "Test1", vbTextCompare) <> 0 Or 
       InStr(1, Cells(i, 10).Value, "Test2", vbTextCompare) <> 0 Or 
       InStr(1, Cells(i, 10).Value, "Test3", vbTextCompare) <> 0 Then

      SplitVal = Split(Cells(i - 2, 10).Value, " ", 2)
      Cells(i + OutputOffset, 13).Value = SplitVal(0)
      Cells(i + OutputOffset, 14).Value = SplitVal(1)

      Cells(i + OutputOffset, 15).Value = Cells(i + 1, 10).Value
    End If
Next i


End Sub

大家好.如您所见，我的代码通过并检查了Test1，Test2或Test3.问题是我有50多个帐户，我不需要检查3个！

Hey everyone. So as you can see my code goes through and checks for Test1,Test2,or Test3. Problem is I have 50+ accounts I need checking not 3!

如何创建和填充列表，创建一个可以复制上面内容的函数，并使用该函数迭代列表?

How do I create and populate a list, make a function that replicates what I have above, and iterate the list using the function?

非常感谢大家！

推荐答案

构建一个包含50种可能循环的数组.找到一个就退出循环.

Build an array of the 50 possibles to loop through. Exit the loop as soon as one is found.

Option Explicit

Sub test()

    Dim DataRange As Range
    Dim lastRow As Long
    Dim i As Integer
    Dim SplitVal() As String
    Dim OutputOffset As Long
    Dim v As Long, tests As Variant
    OutputOffset = 0

    tests = Array("Test1", "Test2", "Test3", "Test4", "Test5", "Test6", "Test7", "Test8", "Test9", _
                  "Test10", "Test11", "Test12", "Test13", "Test14", "Test15", "Test16", "Test17", "Test18", _
                  "Test19", "Test20", "Test21", "Test22", "Test23", "Test24", "Test25", "Test26", "Test27")

    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "J").End(xlUp).Row

        For i = 2 To lastRow
            For v = LBound(tests) To UBound(tests)
                If CBool(InStr(1, .Cells(i, 10).Value2, tests(v), vbTextCompare)) Then Exit For
            Next v

            If v <= UBound(tests) Then
                SplitVal = Split(.Cells(i - 2, 10).Value2, " ", 2)
                .Cells(i + OutputOffset, 13).Value = SplitVal(0)
                .Cells(i + OutputOffset, 14).Value = SplitVal(1)
                .Cells(i + OutputOffset, 15).Value2 = .Cells(i + 1, 10).Value2
            End If
        Next i
    End With

End Sub

我已经添加了一些父工作表引用.

I've added in some parent worksheet references.

这篇关于而不是键入一堆“或"，语句，如何在此代码中实现功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！