|
请高手帮忙,下面三个代码是有规律的,只有四处不同(红色部分),能不能把把它们合并成一个?谢谢了!
Sub test1()
Set d = CreateObject("scripting.dictionary")
With Sheet5
arr = .Range("a4:s" & .Cells(Rows.Count, 1).End(xlUp).Row)
ReDim brr(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arr) - 1
If arr(i, 3) = arr(i + 1, 3) Then
n = n + 1
d(arr(i, 3)) = n + 1
Else
n = 0
End If
Next
For i = 1 To UBound(arr)
If d.Exists(arr(i, 3)) Then
brr(i, 1) = arr(i, 19) / d(arr(i, 3))
Else
brr(i, 1) = arr(i, 19)
End If
Next
.[S4].Resize(UBound(brr), 1) = brr
End With
End Sub
Sub test2()
Set d = CreateObject("scripting.dictionary")
With Sheet5
arr = .Range("a4:q" & .Cells(Rows.Count, 1).End(xlUp).Row)
ReDim brr(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arr) - 1
If arr(i, 3) = arr(i + 1, 3) Then
n = n + 1
d(arr(i, 3)) = n + 1
Else
n = 0
End If
Next
For i = 1 To UBound(arr)
If d.Exists(arr(i, 3)) Then
brr(i, 1) = arr(i, 17) / d(arr(i, 3))
Else
brr(i, 1) = arr(i, 17)
End If
Next
.[Q4].Resize(UBound(brr), 1) = brr
End With
End Sub
Sub test3()
Set d = CreateObject("scripting.dictionary")
With Sheet5
arr = .Range("a4:r" & .Cells(Rows.Count, 1).End(xlUp).Row)
ReDim brr(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arr) - 1
If arr(i, 3) = arr(i + 1, 3) Then
n = n + 1
d(arr(i, 3)) = n + 1
Else
n = 0
End If
Next
For i = 1 To UBound(arr)
If d.Exists(arr(i, 3)) Then
brr(i, 1) = arr(i, 18) / d(arr(i, 3))
Else
brr(i, 1) = arr(i, 18)
End If
Next
.[r4].Resize(UBound(brr), 1) = brr
End With
End Sub
本帖最后由 橘子红 于 2015-4-5 00:10 编辑
没表,没法测试 - Sub test1()
- Set d = CreateObject("scripting.dictionary")
- With Sheet5
- For lastcol = 17 To 19
- arr = .Range(.Range("a4"), Cells(.Cells(Rows.Count, 1).End(xlUp).Row, lastcol))
- ReDim brr(1 To UBound(arr), 1 To 1)
- For i = 1 To UBound(arr) - 1
- If arr(i, 3) = arr(i + 1, 3) Then
- n = n + 1
- d(arr(i, 3)) = n + 1
- Else
- n = 0
- End If
- Next
- For i = 1 To UBound(arr)
- If d.Exists(arr(i, 3)) Then
- brr(i, 1) = arr(i, lastcol) / d(arr(i, 3))
- Else
- brr(i, 1) = arr(i, lastcol)
- End If
- Next
- .Cells(4, lastcol).Resize(UBound(brr), 1) = brr
- Next
- End With
- End Sub
复制代码
|
|