請較各位大大:此段程式是之前前輩提供的程式,功能是判斷sheet1 C欄的股票成交量最新一筆變動資料,若>100,則將sheet1 C欄的股票成交量最新一筆變動資料整列依序填入sheet2 C3,C4,C5...,如圖最新一筆為sheet2 C11,請教各位大大我要如何更改程式讓最新一筆變動的資料永遠在sheet2的C3,最新第二筆往下移一格在sheet2 C4,最新第三筆在sheet2 C5...依此類推!煩請各位大大協助,謝謝!
1. Dim AR
2. Private Sub Worksheet_Calculate()
3. Dim i As Integer
4. i = 2
5. Do While i <= Cells(2, "C").End(xlDown).Row
6. If Not IsError(Cells(i, "C")) Then
7. If Cells(i, "C") > 100 Then
8. With Sheets("sheet2").[C65535].End(xlUp).Offset(1).Resize(, 3)
9. If IsEmpty(AR) Then
10. .Value = Cells(i, "A").Resize(, 3).Value
11. ElseIf Cells(i, "C") <> AR(i - 1, 1) Then
12. .Value = Cells(i, "A").Resize(, 3).Value
13. End If
14. End With
15. End If
16. End If
17. i = i + 1
18. Loop
19. AR = Range(Cells(2, "C"), Cells(2, "C").End(xlDown))
20. End Sub |
發表於 2015-6-20 16:44
| 
