VBA数组中存储单元格值的类型不匹配

Question

这适用于MsgBox，但当我取消对赋值语句的注释时，我会得到类型不匹配错误。我有一个未知长度的字符串，从D1开始，我想存储在数组MyArr中。

Dim MyArr As Variant   
Range("D1").Select
I = 1
While ActiveCell <> Empty
    MsgBox ("this is in the active cell:" & ActiveCell.Value)
'   MyArr(I) = ActiveCell.Value
    I = I + 1
    ActiveCell.Offset(1, 0).Select
Wend

Answer 1

MyArr(I)将失败，因为MyArr没有被定义为数组。查看您的代码，似乎您希望MyArr包含从D1到第一个空单元格

找到的所有字符串。

Dim MyArr
MyArr=Range("D1", Range("D1").End(xlDown))
If VarType(MyArr)>vbArray then 'more than 1 cell returned
    'D1 is in MyArr(1,1)
    'D2 is in MyArr(2,1)
    '...
    'Lastcell is in MyArr(Ubound(MyArr),1)
Else 'Only one cell found with text
    'D1 is in MyArr 
    'note no () -> one cell = no array
End If