|
研究了一下你的公式,读懂了需求,试试这个吧,仅需要 B 到 K 列,其他都可以删除了。
- Sub test()
- Dim srcData As Variant, tmpData As Variant, tarData As Variant
- Dim intCurr As Integer, intNext As Integer, intLen As Integer
- Dim i As Integer, n As Integer
-
- '将原始数据(B4到K53)读入数组
- srcData = Sheets("Sheet1").Range("B4").CurrentRegion
-
- '取数组一维长度
- intLen = UBound(srcData, 1)
-
- '创建临时数组与结果数组,数组长度与 srcData 一维等长
- ReDim tmpData(1 To intLen)
- ReDim tarData(1 To intLen)
- For i = 1 To intLen
- tmpData(i) = 0
- tarData(i) = 0
- Next
-
- '指定从哪列数据开始计算,假设从第5列开始,也就是表中的 F 列
- intCurr = 5
-
- '判断次列的序号
- If intCurr = 10 Then
- intNext = 1
- Else
- intNext = intCurr + 1
- End If
-
- '按题意,循环 7 次
- For n = 1 To 7
- '当前列与次列相乘取个位数
- For i = 1 To intLen
- tmpData(i) = srcData(i, intCurr) * srcData(i, intNext) Mod 10
-
- '每次保留最大的值
- If tmpData(i) > tarData(i) Then
- tarData(i) = tmpData(i)
- End If
- Next
- Next
-
- '将结果写入工作表
- Sheets("Sheet1").Range("AF4").Resize(intLen, 1) = WorksheetFunction.Transpose(tarData)
- End Sub
复制代码 |
|