本帖最后由 JLxiangwei 于 2013-5-14 09:46 编辑
FormulaR1C1 是公式输入方法中括号表示的是相对于选定单元格的相对偏移量,”-”为向左或向上偏移,正数为右或下偏移。无中括号表示的是相对于选定单元格的绝对偏移量,没有负数。“R”和”C”表示的是待变“行”和“列”。 如:选定单元格为C8
R[-1]C[-1]为B7单元格,行列都-1,R[1]C[2]为E9单元格,行+1,列+2
R1C1代表A1单元格,R5C6代表F5单元格
例:C1单元格为”=A1+B1″
Range(“C1″).FormulaR1C1 = “=RC[-2]+RC[-1]”
例:C1单元格为”=A2+E3″
Range(“C1″).FormulaR1C1 = “=R[1]C[-2]+R[2]C[2]“
子过程中&符号前面后面加空格
'子过程,生成矩阵表
Public Sub BuildMatrix(ws As Worksheet, n, a, b As Integer)
'ws是工作表,n为叶子表,(a,b)为生成矩阵的起始位置
'生成矩阵
ws.Cells(a, b).Interior.ColorIndex = 39
ws.Range(ws.Cells(a, b + 1), ws.Cells(a, b + n)).Interior.ColorIndex = 36
ws.Range(ws.Cells(a + 1, b), ws.Cells(a + n, b)).Interior.ColorIndex = 36
'对生成的表头赋色
For i = 1 To n
ws.Cells(a + 1, b + 1).Interior.ColorIndex = 34
ws.Cells(a + 1, b + 1).Value = 1
Next i '对矩阵对角线赋色及值1
For i = 2 To n
For j = 1 To i - 1
ws.Range(ws.Cells(a + i, b + 1), ws.Cells(a + i, b + j)).Interior.ColorIndex = 38
'把中需要输入矩阵的位置填色\下三角
ws.Range(ws.Cells(a + 1, b + i), ws.Cells(a + j, b + i)).Interior.ColorIndex = 40
'把中需要输入矩阵的位置填色\上三角
ws.Cells(a + j, b + i).FormulaR1C1 = "=1/R[" & i - j & "]C[-" & i - j & "]" '转秩位置的值互为倒数。即aij=1/aji
Next i
Next i
End Sub
'子过程,复制表头
Public Sub CopyNameTo(ws As Worksheet, n, a, b As Integer)
'ws是工作表,n为叶子数,(x,y)为树状表的起始位置,(a,b)为生成矩阵的起始位置
ws.Cells(a + 0, b + 0).FormulaR1C1 = "SetUp!R" & x + 1 & "C" & y + 0 & "" '给(0,0)附名称
For i = 1 To n
ws.Cells(a + i, b + 0).FormulaR1C1 = "=SetUp!R" & x + 2 * i - 1 & "C" & y + 1 & ""
ws.Cells(a + 0, b + i).FormulaR1C1 = "=SetUp!R" & x + 2 * i - 1 & "C" & y + 1 & ""
Next i
ws.Cells(a + n + 1, b + 0).FormulaR1C1 = "最大特征值"
ws.Cells(a + n + 2, b + 0).FormulaR1C1 = "CI"
ws.Cells(a + n + 3, b + 0).FormulaR1C1 = "RI"
ws.Cells(a + n + 4, b + 0).FormulaR1C1 = "CR"
ws.Cells(a + n + 5, b + 0).FormulaR1C1 = "判断一致性"
ws.Cells(a + 0, b + n + 1).FormulaR1C1 = "原始权重"
End Sub
第二:在CopyNameTo中定义了四个参数,在主程序BuildTable调用时Call CopyNameTo(ws, n, x, y, a) 却设置了五个参数。
Sub BuildTable() '生成树状结构表及相应的矩阵
Dim n, x, y As Integer
n = ActiveCell.Value
x = ActiveCell.Row
y = ActiveCell.Column
'确定激活单元格的行列、起始位置的坐标(x,y)以及下层叶子数量n
Dim m, a, b As Integer
a = 2
b = 4 '(2,4)是矩阵最初的起始位置
m = NumB(x, y)
'子函数,确定u的值,即所操作单元格之前有几个叶子
a = a + (x - 12) / 2 + 7 * (m - 1)
b = b '确定所有操作单元格相应矩阵的起始位置(a,b)
Dim num, ws As Worksheet
num = Range("C10").Value
'"C10"是确定层数的位置,m确定的是层数
For i = 1 To num - 1
If y + 1 = 3 + i Then '3为起始列
Set ws = ThisWorkbook.Worksheets(2 + i)
'设置激活相应的表
End If
Next i
If n >= 1 And x >= 6 And y > 2 And y < (2 + num) Then
'从第6行开始,叶子数不为1为树状结构的开始行
If IsEmpty(Worksheets(2).Cells(x + 1, y)) Then
MsgBox "因子名称不能为空,请重新输入"
Else
Call BuildTree(n, num, x, y) '调用子过程,生成树状表
Call BuildMatrix(ws, n, a, b) '调用子过程,生成矩阵表
Call CopyNameTo(ws, n, x, y) '调用子过程,复制表头
End If
End If
{:912:} |