type mismatch storing cell value in VBA array-Collection of common programming errors

MyArr(I) will fail, as MyArr has not been defined as an array. Looking at your code, it seems you would like MyArr to contain all the strings found from D1 down to the first empty cell

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

Originally posted 2013-11-09 18:56:45.