Looping controls and array redim

mega · April 19th, 2005, 09:36 AM

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.
[CODE]
' 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
    Else
      myArray(counter, i) = oCell.Interior.Color
      i = 0
      counter = counter + 1
    End If
Next oCell
[CODE]
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:

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:

Code:

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
;)

- mega
Moving to C# .NET

mega · April 19th, 2005, 10:19 AM

I'm trying to update a workbook with data from SAP workbooks. I've made query from SAP in October and know it's pretty much outdated. What I want to do is get the data from my newest SAP queries and update the new fields, but I also want to suppress an update if the interior color isn't white (a background color is given to each row to keep track of its status). Instead of just using the Range object I want to use arrays because of the speed that opt gives me. The code for getting the data from the updated workbook was pretty much straight forward but this part is messy.

- mega
Moving to C# .NET

mega · April 19th, 2005, 11:57 AM

I finally sorted it out my self. You can see how here: http://www.ozgrid.com/forum/showthread.php?p=164762

- mega
Moving to C# .NET