如何在VBA Excel中循环一个矩阵?

I am trying to make a macro that will create a matrix based upon an equation in an VBA.

我正在尝试做一个宏，它将基于VBA中的一个方程创建一个矩阵。

For example: If I have the following 3x3 matrix:

例如:如果我有以下3x3矩阵:

3  5  7
6  3  4
1  2  3

I want to create a 3x3 matrix that takes the value 1st value and divides it by the sum of the row, and so on.

我想创建一个3x3矩阵它取值1除以行和，以此类推。

0.2  0.3  0.5
0.5  0.2  0.3
0.2  0.3  0.5

I tried the following code:

我尝试了以下代码:

Sheets("sheet1").Select
Range("C2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select 'So the new matrix starts underneath the old matrix
Dim i As Integer
Dim n As Integer
i = 4
n = 1
Do While Cells(i, 3).Value <> ""
    ActiveCell.FormulaRnC1 = "=RiC3/SUM(RiC3:RiC52)*100"
    i = i + 1
ActiveCell.Offset(1, 0).Range("A1").Select

The number or rows will vary.

数字或行会有所不同。

I have limited experience with this platform, please let me know how to improve framing this question.

我对这个平台的经验有限，请告诉我如何改进这个问题。

1 个解决方案

#1

I will recommend you put your first matrix on Sheet1 starting in cell A1. This will output the other Matrix to the new workbook, Sheet1, starting in cell A1.

我建议你把你的第一个矩阵放在Sheet1上，从A1单元开始。这将把另一个矩阵输出到新的工作簿Sheet1，从单元格A1开始。

Sub example()
Dim x As Variant, y As Variant
Dim row_sum As Double
Dim i As Integer, j As Integer
Dim wbk As Workbook

With ThisWorkbook.Sheets(1)
    x = .Range("a1:" & .Cells(.Range("a" & .Rows.Count).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Address).Value2
End With

y = x

With Application.WorksheetFunction
    For i = LBound(x, 1) To UBound(x, 1)
        row_sum = .Sum(.Index(x, i))
            For j = LBound(x, 2) To UBound(x, 2)
                y(i, j) = x(i, j) / row_sum
            Next j
    Next i
End With

Set wbk = Workbooks.Add

wbk.Sheets(1).Range("a1").Resize(UBound(y, 1), UBound(y, 2)) = y


End Sub