每个范围和拆分问题的 Excel vba

KC石

请帮助解决我的问题。我使用下面的脚本，但在 activesheet 上没有任何结果..

 Sub Dutylist()
 Dim dutyTable(1 To 2, 1 To 4) As String
 Dim Cyc As Integer, Team As Integer
 Dim Svalue As Range, Srange As Range
 Dim Result() As String
 //Cycle 1
 dutyTable(1, 1) = "A,B,C,D,E,F,G"
 dutyTable(1, 2) = "D,C,A,B,A,E,D"
 dutyTable(1, 3) = "B,A,E,C,B,D,E"
 dutyTable(1, 4) = "C,B,C,D,C,A,A"
 //Cycle 2
 dutyTable(2, 1) = "B,E,D,A,D,B,A"
 dutyTable(2, 2) = "B,A,E,C,C,D,B"
 dutyTable(2, 3) = "D,C,A,B,B,E,B"
 dutyTable(2, 4) = "E,A,B,D,A,C,D"

 Cyc = 1
 Set Srange = ActiveSheet.Range("a:a")
 For Team = 1 To 4
      Result = Split(dutyTable(Cyc, Team), ",")
    For Each Svalue In Srange
       If Svalue = "Team " & CStr(Team) Then
          Svalue.Offset(0, 1).Resize(, UBound(Result) + 1).Value = Result
       End If
    Next Svalue
 Next Team
 End Sub

我上面的代码有问题吗？

健祥

我对您的代码执行的理解应该如下，我所做的更改是重新排列并Looping进行一些修改，并且您应该set range参考上次使用的行而不是A:A加快执行速度。

基本上代码将匹配Col A团队的值，如果找到，则dutyTable1根据cycle您提供的返回值，

 Sub Dutylist()
 Dim dutyTable(1 To 2, 1 To 4) As String
 Dim Cyc As Integer, Team As Integer
 Dim Svalue As Range, Srange As Range
 Dim Result() As String
 Dim i As Long

 dutyTable(1, 1) = "A,B,C,D,E,F,G"
 dutyTable(1, 2) = "D,C,A,B,A,E,D"
 dutyTable(1, 3) = "B,A,E,C,B,D,E"
 dutyTable(1, 4) = "C,B,C,D,C,A,A"

 dutyTable(2, 1) = "B,E,D,A,D,B,A"
 dutyTable(2, 2) = "B,A,E,C,C,D,B"
 dutyTable(2, 3) = "D,C,A,B,B,E,B"
 dutyTable(2, 4) = "E,A,B,D,A,C,D"

 Cyc = 1
 Set Srange = ActiveSheet.Range("a1:a20")

For Each Svalue In Srange
    For Team = 1 To 4
        Result = Split(dutyTable(Cyc, Team), ",")
            If InStr(Svalue.Value, Team) > 0 Then
               Svalue.Offset(0, 1).Resize(, UBound(Result) + 1).Value = Result
            End If
    Next Team
Next Svalue

 End Sub

输出（仅测试 3 个团队，可扩展）