CODEWITHSUNDEEP
excelvba
KMLN
KMLN

Reputation: 79

Working with an array of variants

I have declared a variant variable in my VBA code, which takes on values from a range. E.g.

Dim Output as variant
Output = Range("A1:Z1").value

I have declared another array of variants.

Dim OutputArray(10000) as variant

Each item in this array is an iteration of Output; i.e. OutputArray(0) is the value of Output in the first iteration, OutputArray(1) is the value of Output in the second iteration and so on.

I have two questions:

  1. How can I extract individual items from OutputArray; e.g. I want to know the value of cell C1 in 27th iteration?

  2. How can I paste the entire array OutputArray on a worksheet in one go? If it is not possible to do so in one go, what's the best option I have?

Thanks in advance!

Upvotes: 0

Views: 203

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

To return C1 on the 27th iteration, it will be something like

OutputArray(26)(1,3)

I think you will have to paste each iteration separately

  • Set the first cell of the range (upper left corner)
  • Resize the range to include the size of your iteration.
    • If they are all the same size, you can use a constant; if not, you will have to compute it.
  • R = outputArray(iteration_num)
  • Increment FirstCell depending on where you want the next iteration
  • Repeat

Upvotes: 3

Related Questions