My problem is two fold and if you only know the answer to one of them I'll appreciate it (almost) just as much.
What I want to do is to populate an array based on two indexes. The first being the number of rows in my Range, the latter being the number of columns in my Range plus one. The extra one is the interior color of the row/cell. The code below shows what I've come up with so far.
' The spreadsheet data is in n
number of rows and 7 columns
counter = 1
For Each oCell In myRange
i = i + 1
ReDim Preserve myArray(counter, i)
If i < (myRange.Columns.Count) + 1 Then
myArray(counter, i) = oCell
myArray(counter, i) = oCell.Interior.Color
i = 0
counter = counter + 1
While this is fine the first iteration, it doesn't work the next because I can't stop/hold the For Each loop and insert the extra data. I simply loose the first cell value in the next row.
My second problem is how I dim and redim an arrray. When I get to the second iteration I get a
quote:Run-time error '9':
Subscript out of range
I obviously can't dim my array with the desired values because one need to use constants and I don't know how many rows I need to add to my array.
So I just Dim it like this:
Dim myArray() As Variant
Any help would be a life saver - need to make this work within this week at latest. Thanks in advance - mega
Moving to C# .NET