Dim Sh_Temp As Worksheet
Set Sh_Temp = ThisWorkbook.Sheets("Temp")
lRow = Sh_Temp.Cells(Rows.Count, 1).End(xlUp).Row
ReDim vArray(lRow - 1)
vArray = Sh_Temp.Range("A2:A" & lRow).Value2 ' Exclude Header Value in A1
Above code works perfectly well when there is more than 1 value in worksheet 'Temp' but when there is only one value e.g.
Ref. No.
2.1.a
can not copy into array it gives an error
'type mismatch'
Where is the need for ReDim vArray(lRow - 1)? At vArray = Sh_Te... the bounds will automatically be set. Also if there is only one value, then it will just return a value and not an array. But with your ReDim vArray(lRow - 1) you try to insert a value in an array without a position.
You can choose to use it like this:
Dim Sh_Temp As Worksheet
Set Sh_Temp = ThisWorkbook.Sheets("Temp")
lRow = Sh_Temp.Cells(Rows.Count, 1).End(xlUp).Row
Dim vArray As Variant
vArray = Sh_Temp.Range("A2:A" & lRow).Value2
Then vArray will be no array if only one value is returned, or do it like this:
Dim Sh_Temp As Worksheet, lRow As Long, vArray As Variant
Set Sh_Temp = ThisWorkbook.Sheets("Temp")
lRow = Sh_Temp.Cells(Rows.Count, 1).End(xlUp).Row
If lRow <= 2 Then
vArray = Array()
ReDim vArray(1 To 1, 1 To 1)
vArray(1, 1) = Sh_Temp.Range("A2").Value2
Else
vArray = Sh_Temp.Range("A2:A" & lRow).Value2
End If
then vArray will be a 2D-array no matter what.
ReDim vArray(1,lRow - 1), andlrow-1must never be 0 or lower.