vba - 将值分配给数组时，类型不匹配错误

如果数据显示为红色，则将其存储在阵列中并显示在MSGBOX中。但由于我遇到类型不匹配错误而无法执行。

             Name         Age        Sex

             John         18          m
             Rita         15          f -------> RED
             Phil         15          m -------> Red
             Letty        15          f --------> Red
             and so on

Sub msg()
Dim dar() As Variant
Dim j3 As Variant
r3 = Range("a1").SpecialCells(xlCellTypeLastCell).Row
For i3 = 1 To r3
    If (Range("E" & i3).Interior.Color = RGB(255, 0, 0)) = True Then
        j3 = j3 + 1

        ' Getting Error at this line Type Mismatch error
        Set dar(i3) = Range("b" & i3, "f" & i3).Value

        MsgBox x
    End If
Next i3
End Sub

最佳答案

失去Set关键字。
Set x(i3) = Range("b" & i3, "f" & i3).Value更改为
x(i3) = Range("b" & i3, "f" & i3).Value
Set关键字用于分配对象，您正在尝试分配值。这就是错误的原因。

编辑1

我们忘了初始化数组。

Sub msg()
Dim dar() As Variant
Dim j3 As Variant
r3 = Range("a1").SpecialCells(xlCellTypeLastCell).Row
For i3 = 1 To r3
    If (Range("E" & i3).Interior.Color = RGB(255, 0, 0)) = True Then
        ReDim Preserve dar(j3)
        dar(j3) = Range("b" & i3).Value    'beware, that I have changed the dar(i3) to dar(j3)
        j3 = j3 + 1
        MsgBox x
    End If
Next i3

End Sub

编辑2

Sub msg()
Dim dar() As Variant
Dim j3 As Variant

r3 = Range("a1").SpecialCells(xlCellTypeLastCell).Row
For i3 = 2 To r3 'changed to start at row 2, in row 1 there are headers I guess
    If (Range("E" & i3).Interior.Color = RGB(255, 0, 0)) = True Then

        ReDim Preserve dar(j3)
        dar(j3) = Range(Cells(i3, 1), Cells(i3, 6))

        'store the values from this row only into string
        For y = 1 To 6
            If y = 1 Then
                strMessage = dar(j3)(1, y)
            Else
                strMessage = strMessage & " || " & dar(j3)(1, y)
            End If
        Next y

        j3 = j3 + 1

        'store all previous strings one string, with multiple rows
        If j3 = 1 Then
            strMessage2 = strMessage
        Else
            strMessage2 = strMessage2 & Chr(10) & strMessage 'chr(10) is a line break
        End If

    End If
Next i3

If Not IsEmpty(strMessage2) Then
    MsgBox strMessage2, Title:="Red data"
End If

End Sub