|
楼主 |
发表于 2010-8-31 21:01
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
原帖由 secowu 于 2010-8-31 20:20 发表
谢谢,辛苦了。
那个value的原因是因为“”的值。
看来,得从其它地方处理这个“”了
解决了,感谢zhangjimfu的热心帮助。
- Option Explicit
- option private module
- Sub mSumproductAddress()
- Dim i, j, k As Integer
- Dim row As Integer
- Dim mSum As String
- On Error Resume Next
- row = 2
- k = ActiveSheet.Cells(Rows.Count, 1).End(3).row + 2
- For j = 3 To ActiveSheet.UsedRange.Columns.Count
- For i = 1 To k - 4 Step 2
- mSum = mSum & Cells(i + 2, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "*" & Cells(row, j).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+"
- row = row + 2
- Next i
- Cells(k, j) = "=" & Left(mSum, Len(mSum) - 1)
- i = 1
- row = 2
- mSum = ""
- Next j
- End Sub
- Sub mSumproduct()
- Dim i, j, k As Integer
- Dim row As Integer
- Dim mSum As Integer
- On Error Resume Next
- row = 2
- k = ActiveSheet.Cells(Rows.Count, 1).End(3).row + 1
- For j = 3 To ActiveSheet.UsedRange.Columns.Count
- For i = 1 To ActiveSheet.Cells(Rows.Count, 1).End(3).row Step 2
- mSum = mSum + Cells(i + 2, 1) * Cells(row, j)
- row = row + 2
- Next i
- Cells(k, j) = mSum
- i = 1
- row = 2
- mSum = 0
- Next j
- End Sub
- Sub temp()
- Call mSumproduct
- Call mSumproductAddress
- End Sub
复制代码 |
|