array - "Type mismatch"

[almagg]

Issues are numbered in the below code. Thanks for the help.
1) When I set both above dimensions to 'Integer' this line errrors as 'Type mismatch'

2) I get no value for 'myvalue' in the Watch window but I let a cell equal this value and it was correct.

3) program runs up to here and then I get a 'Type mismatch'

Dim myarray() As Variant
Dim myvalue As Variant

Range(Selection, Selection.End(xlDown)).Select
elements = Selection.Count
ReDim myarray(elements, 1)
For i = 1 To elements
.....myarray(i, 1) = Selection.Offset(i - 1, 0).Value ***1
Next i

For i = 1 To elements - 1
.....myvalue = myarray(i, 1) ***2
.....For j = 1 To elements - 1
..........If myvalue > myarray(j + 1, 1) Then ***3
etc...

 

[GlennUK]

You change the selection to be a range, and then use offset on it, and appear to be assuming that will return a single cell ... it won't. This would do better:

Sub mission()
Dim myarray() As Integer
Dim myvalue As Integer

Set mydata = Range(Selection, Selection.End(xlDown))
elements = Selection.Count
ReDim myarray(elements, 1)
For i = 1 To elements
  myarray(i, 1) = mydata.Cells(i, 1).Value '***1
Next i

For i = 1 To elements - 1
   myvalue = myarray(i, 1) '***2
   For j = 1 To elements - 1
     If myvalue > myarray(j + 1, 1) Then '***3
        '
        End If
    Next
Next