Q:

Excel - VBA Converting matrix entries to percentages of row total

I have a matrix with an equal number of rows and columns of size numColors. The following code attempts to convert each entry from an integer to a percent of its row total. I receive a "Subscript out of range" error and need help fixing it. Thanks.

For i = 2 To numColors + 2
rowSum = 0
tempArray = Range(Cells(i, 2), Cells(i, numColors + 1))
    For j = LBound(tempArray) To UBound(tempArray)
        rowSum = rowSum + tempArray(j)
    Next
    For j = LBound(tempArray) To UBound(tempArray)
        tempArray(j) = tempArray(j) / rowSum
    Next
Range(Cells(i, 2), Cells(i, numColors + 1)) = tempArray
Next

A:

Try this:
For i = 2 To numColors + 1
    rowSum = 0
    tempArray = Range(Cells(i, 2), Cells(i, numColors + 1))
    For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        rowSum = rowSum + tempArray(1, j)
    Next j
    For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        tempArray(1, j) = tempArray(1, j) / rowSum
    Next j
    Range(Cells(i, 2), Cells(i, numColors + 1)) = tempArray
Next i

the reason is even though it is one row, tempArray is a 2D array.

Here is a slight modification to your code.  It loads the whole matrix into the array, manipulates the data then puts the values back as a whole:
tempArray = Range(Cells(2, 2), Cells(numColors + 1, numColors + 1))
For i = LBound(tempArray, 1) To UBound(tempArray, 1)
    rowSum = 0
    For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        rowSum = rowSum + tempArray(i, j)
    Next j
    For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        tempArray(i, j) = tempArray(i, j) / rowSum
    Next j
Next i
Range(Cells(2, 2), Cells(numColors + 1, numColors + 1)).value = tempArray

In this instance it will not make much difference in speed, but if the matrix were bigger, the fewer times referring to the spread sheet the better.

