Excel VBA Extracting Data from Table to an Array

[tharrington]

Im trying to extract the color index of cells in a table in a spreadsheet into an array using a VBA macro. The indexing I have for my for loop is offset as in the count starts at column 2 and row 4, but the array begins at 0,0 or 1,1? Is there a way to enter this info using such a for loop?

Thanks

 

[offthelip]

does this do what you want?? Note I didn't know how many columns or rows you wanted so I assume up to row and column 10

Sub test2()
Dim inarr(1 To 9, 1 To 7) As Variant


For irow = 2 To 10
  For icol = 4 To 10
    inarr(irow - 1, icol - 3) = Cells(irow, icol).Interior.ColorIndex
  Next icol
Next irow
End Sub

 

[tharrington]

Dim j As Integer
Dim i As Integer
For i = 4 To numrows + 3
    For j = 2 To 12 'columns
    If ActiveSheet.Cells(i, j).Interior.ColorIndex = 6 Then
    Range("M" & i).Value = Range("M" & i).Value + 1
    Cells(i, j).Interior.ColorIndex = cArray(i - 3, j - 1)
    End If
    Next j
    Next i

Here is my code,

That indexing did not work I believe

 

[Rick Rothstein]

Dim j As Integer
Dim i As Integer
For i = 4 To numrows + 3
    For j = 2 To 12 'columns
    If ActiveSheet.Cells(i, j).Interior.ColorIndex = 6 Then
    Range("M" & i).Value = Range("M" & i).Value + 1
    Cells(i, j).Interior.ColorIndex = cArray(i - 3, j - 1)
    End If
    Next j
    Next i

Here is my code,

That indexing did not work I believe

The code you posted above has nothing to do with the question you asked in Message #1 . In Message #1 , you said you wanted to fill an array with the color index from the cells in part of a table... the code above is filtering only yellow filled cells and changing that yellow color to a value stored in an array named cArray. What exactly do you want?

 

[tharrington]

Apologies for that. I have existing code that counts the number of yellow cells. I figured that I would add in this array part in the same for loop to save code. I want a final count of yellows, and an array of the color index. Is that possible?