Using a Variant Array in a loop (error 13 type mismatch)

Question

I'm doing a for loop and need to use a Variant Array type, but i'm getting Error 13 (type mismatch). I want k to be 3, 4 and 5 for the 1st value, and so on (according to variable inspection below). I just don't know how to make vba read the array. I've tried using tsperiodo(i)(j) but it hasn't worked either.

Here is the variant array:

ReDim ocup(1 To numDis, 1 To numSalas) As Variant

For i = 1 To numDis
    For j = 1 To numSalas
        If capacidadeSalas(j) - alunos(i) >= 0 Then
        ocup(i, j) = 1
        End If
    Next
Next    

ReDim tsPeriodo(1 To numDis) As Variant

For i = 1 To numDis
    tsPeriodo(i) = Application.Transpose(Evaluate("=ROW(" & tsInicio(i) & ":" & tsFim(i) & ")"))
Next

and here is where i want to use it:

For i = 1 To ocup(numDis, 1)
    For j = 1 To ocup(1, numSalas)
        For k = 1 To tsPeriodo(i)  'this is where im getting error 13
            variable = "x_" & i & "_" & j & "_" & k
        Next
    Next
Next

variable inspection for the tsperiodo(i)

what version of Excel are you using ? French ? what is For i = 1 To ocup(numDis, 1) suppost to be ? — Shai Rado
– Shai Rado, Commented Nov 23, 2016 at 6:42
I'm using excel 2016 in portuguese! "ocup" it's a variat array too, but that's working properly, i will edit the main post to insert it in the code. — jf8769
– jf8769, Commented Nov 23, 2016 at 6:55
You never show how tsInicio(i) and tsFim(i) are instantiated. Evaluate("=ROW(" & tsInicio(i) & ":" & tsFim(i) & ")") will just return the a number not an array. — user6432984
– user6432984, Commented Nov 23, 2016 at 7:12

A.S.H · Accepted Answer · 2016-11-23 07:03:50Z

1

Without understanding deeply what you are trying to achieve, obviously each element of tsPeriodo is an variant/array itself, so you end up with some kind of a (non-standard) 2D array. To catch all the elements inside it in your loop, you can try this:

Dim k, l
For Each k In tsPeriodo
    For Each l In k
      ' doSomething, i.e.
      variable = "x_" & i & "_" & j & "_" & l

    Next
Next

answered Nov 23, 2016 at 7:03

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jf8769 Over a year ago

It worked, thank you! Is there another way to retrieve the variables from a variant type array or this is the only one?

A.S.H Over a year ago

Usually you can also index it in the usual way, but you need to know the number of dimensions, the size of each element, etc. With For Each you need less prior knowledge.

Collectives™ on Stack Overflow

Using a Variant Array in a loop (error 13 type mismatch)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related