Array - 'Type mismatch'

[almagg]

i don't know what the problem is.
but i noticed that the variable 'myvalue' shows up in the 'Watch' window as an array.
which seems weird since i have not declared it as an array.

Option Base 1
Dim myarray()
Dim myvalue

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

For i = 1 To elements - 1
.....myvalue = myarray(i)
.....Range("D1") = myvalue 'THE CORRECT VALUE SHOWS UP IN D1. JUST A TEST
.....For j = 1 To elements - 1
..........If myvalue > myarray(j + 1) Then 'HERE I GET THE 'Type Mismatch' ERROR
...ETC.
can't go any further till i fix the above erro.

any ideas at all?
aaaack

 

[mikerickson]

Is it possible that myvalue or myarray(j+1) is an error value (e.g. #DIV/0)

If so, the comparison CStr(myvalue) > CStr(myarray(j+1) should be used to convert the error value into a data type that > can handle, unless you are expecting numbers, in which case Val(CStr(...)) can be useds

 

[almagg]

well i set up a short test list of 4 numbers in cells A1:A4
6,4,5,2
just to initially test the program.

and when i put the values of 'myvalue' and 'myarray(j + 1)' to check, the correct numbers appear.

i just tried bot CStr and Val(CStr(...
and still get the 'Type Mismatch' error

i just dunno

 

[almagg]

also just to make something clear.

the variable 'myvalue' shows up in the Watch window as an array.
??

 

[mikerickson]

I think I found the problem

Range(Selection, Selection.End(xlDown)).Select

elements = Selection.Count

ReDim myarray(elements)
For i = 1 To elements
    myarray(i) = Selection.Offset(i - 1, 0).Value
Next i

The first line makes Selection a multi-cell range
The line myarray(i) = Selection.Offset(i - 1, 0).Value takes the value of that (offset) array and puts in in myarray(i). So myArray is an array, each element of which is an array.

You might change that code to

Range(Selection, Selection.End(xlDown)).Select

elements = Selection.Count

ReDim myarray(elements)
For i = 1 To elements
    myarray(i) = Selection.Cells(i,1).Value
Next i

Or you could avoid the re-Selection entirly.

With Range(Selection, Selection.End(xlDown))
    elements = .Rows.Count

    ReDim myarray(1 to elements)

    For i = 1 To elements
        myarray(i) = .Cells(i,1).Value
    Next i
End With

 

[almagg]

that was it!
i see what you were saying.
thanks