|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
要对类似空白的地方双击后,才OK
- 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
复制代码
[ 本帖最后由 secowu 于 2010-8-31 20:59 编辑 ] |
|