string - VBA 范围和字符串混淆

Question

我认为我对范围和字符串之间的差异有疑问。尝试让它在范围内的单元格中搜索任何获胜者，然后吐出一个分值。

Sub it()
Dim Round1 As Range
Dim Round2 As Range
Dim lngCnt As Long
Dim Winner() As String
Dim pointSum As Double
ReDim Winner(1 To 4)
    Winner(1) = "Mohler"
    Winner(2) = "Scotter"
    Winner(3) = "DKGAY"
    Winner(4) = "Lassie"
Set Round1 = Range("L3,L11,L22,L32").Text
For Each Winner In Round1
lngCnt = lngCnt + 10
Winner(lngCnt) = pointSum.Value
Next
MsgBox pointSum.Value    
End Sub

Answer 1

您将需要两个循环:

Sub it()
Dim Round1 As Range
Dim Round2 As Range
Dim lngCnt As Long
Dim Winner() As String
Dim pointSum As Double
Dim rng As Range
Dim i As Long
ReDim Winner(1 To 4)
    Winner(1) = "Mohler"
    Winner(2) = "Scotter"
    Winner(3) = "DKGAY"
    Winner(4) = "Lassie"
Set Round1 = Range("L3,L11,L22,L32")
For Each rng In Round1
    For i = 1 To UBound(Winner)
        If rng.Value = Winner(i) Then
            pointSum = pointSum + 10
            Exit For
        End If
    Next i
Next rng

MsgBox pointSum.Value
End Sub

第一个循环遍历范围，第二个遍历字符串数组。当它找到相等的位置时，它会加 10。