麻辣家族討論版版 - Powered by Discuz! Board

標題: 請問如何簡化，謝謝 [打印本頁]

作者: caimy 時間: 2016-3-11 09:58 標題: 請問如何簡化，謝謝

請問如何簡化下列程式，謝謝 ^^

Sub 修改回存()
X = Sheets("明細").[b65536].End(xlUp).Row
S = Sheets("機油總計").[c65536].End(xlUp).Row
For M = 2 To X
If Sheets("明細").Cells(M, 1) = Sheets("加油紀錄輸入修改").Cells(3, 2) Then
Sheets("明細").Cells(M, 2) = Sheets("加油紀錄輸入修改").Cells(5, 2)
Sheets("明細").Cells(M, 3) = Sheets("加油紀錄輸入修改").Cells(5, 3)
Sheets("明細").Cells(M, 4) = Sheets("加油紀錄輸入修改").Cells(6, 2)
Sheets("明細").Cells(M, 5) = Sheets("加油紀錄輸入修改").Cells(6, 3)
Sheets("明細").Cells(M, 6) = Sheets("加油紀錄輸入修改").Cells(7, 2)
Sheets("明細").Cells(M, 7) = Sheets("加油紀錄輸入修改").Cells(7, 3)
Sheets("明細").Cells(M, 8) = Sheets("加油紀錄輸入修改").Cells(8, 2)
Sheets("明細").Cells(M, 9) = Sheets("加油紀錄輸入修改").Cells(8, 3)
Sheets("明細").Cells(M, 10) = Sheets("加油紀錄輸入修改").Cells(9, 2)
Sheets("明細").Cells(M, 11) = Sheets("加油紀錄輸入修改").Cells(9, 3)
Sheets("明細").Cells(M, 12) = Sheets("加油紀錄輸入修改").Cells(10, 2)
Sheets("明細").Cells(M, 13) = Sheets("加油紀錄輸入修改").Cells(10, 3)
Sheets("明細").Cells(M, 14) = Sheets("加油紀錄輸入修改").Cells(11, 2)
Sheets("明細").Cells(M, 15) = Sheets("加油紀錄輸入修改").Cells(11, 3)
Sheets("明細").Cells(M, 16) = Sheets("加油紀錄輸入修改").Cells(12, 2)
Sheets("明細").Cells(M, 17) = Sheets("加油紀錄輸入修改").Cells(12, 3)
Sheets("明細").Cells(M, 18) = Sheets("加油紀錄輸入修改").Cells(13, 2)
Sheets("明細").Cells(M, 19) = Sheets("加油紀錄輸入修改").Cells(13, 3)
Sheets("明細").Cells(M, 20) = Sheets("加油紀錄輸入修改").Cells(14, 2)
Sheets("明細").Cells(M, 21) = Sheets("加油紀錄輸入修改").Cells(14, 3)
Sheets("明細").Cells(M, 22) = Sheets("加油紀錄輸入修改").Cells(15, 2)
Sheets("明細").Cells(M, 23) = Sheets("加油紀錄輸入修改").Cells(15, 3)
Sheets("明細").Cells(M, 24) = Sheets("加油紀錄輸入修改").Cells(16, 2)
Sheets("明細").Cells(M, 25) = Sheets("加油紀錄輸入修改").Cells(16, 3)
Sheets("明細").Cells(M, 26) = Sheets("加油紀錄輸入修改").Cells(17, 2)
Sheets("明細").Cells(M, 27) = Sheets("加油紀錄輸入修改").Cells(17, 3)
Sheets("明細").Cells(M, 28) = Sheets("加油紀錄輸入修改").Cells(18, 2)
Sheets("明細").Cells(M, 29) = Sheets("加油紀錄輸入修改").Cells(18, 3)
Sheets("明細").Cells(M, 30) = Sheets("加油紀錄輸入修改").Cells(19, 2)
Sheets("明細").Cells(M, 31) = Sheets("加油紀錄輸入修改").Cells(19, 3)
Sheets("明細").Cells(M, 32) = Sheets("加油紀錄輸入修改").Cells(20, 2)
Sheets("明細").Cells(M, 33) = Sheets("加油紀錄輸入修改").Cells(20, 3)
Sheets("明細").Cells(M, 34) = Sheets("加油紀錄輸入修改").Cells(21, 2)
Sheets("明細").Cells(M, 35) = Sheets("加油紀錄輸入修改").Cells(21, 3)
Sheets("明細").Cells(M, 36) = Sheets("加油紀錄輸入修改").Cells(22, 2)
Sheets("明細").Cells(M, 37) = Sheets("加油紀錄輸入修改").Cells(22, 3)
Sheets("明細").Cells(M, 38) = Sheets("加油紀錄輸入修改").Cells(23, 2)
Sheets("明細").Cells(M, 39) = Sheets("加油紀錄輸入修改").Cells(23, 3)
Sheets("明細").Cells(M, 40) = Sheets("加油紀錄輸入修改").Cells(24, 2)
Sheets("明細").Cells(M, 41) = Sheets("加油紀錄輸入修改").Cells(24, 3)
Sheets("明細").Cells(M, 42) = Sheets("加油紀錄輸入修改").Cells(25, 2)
Sheets("明細").Cells(M, 43) = Sheets("加油紀錄輸入修改").Cells(25, 3)
Sheets("明細").Cells(M, 44) = Sheets("加油紀錄輸入修改").Cells(26, 2)
Sheets("明細").Cells(M, 45) = Sheets("加油紀錄輸入修改").Cells(26, 3)
Sheets("明細").Cells(M, 46) = Sheets("加油紀錄輸入修改").Cells(27, 2)
Sheets("明細").Cells(M, 47) = Sheets("加油紀錄輸入修改").Cells(27, 3)
Sheets("明細").Cells(M, 48) = Sheets("加油紀錄輸入修改").Cells(28, 2)
Sheets("明細").Cells(M, 49) = Sheets("加油紀錄輸入修改").Cells(28, 3)
Sheets("明細").Cells(M, 50) = Sheets("加油紀錄輸入修改").Cells(29, 2)
Sheets("明細").Cells(M, 51) = Sheets("加油紀錄輸入修改").Cells(29, 3)
Sheets("明細").Cells(M, 52) = Sheets("加油紀錄輸入修改").Cells(30, 2)
Sheets("明細").Cells(M, 53) = Sheets("加油紀錄輸入修改").Cells(30, 3)
Sheets("明細").Cells(M, 54) = Sheets("加油紀錄輸入修改").Cells(31, 2)
Sheets("明細").Cells(M, 55) = Sheets("加油紀錄輸入修改").Cells(31, 3)
Sheets("明細").Cells(M, 56) = Sheets("加油紀錄輸入修改").Cells(32, 2)
Sheets("明細").Cells(M, 57) = Sheets("加油紀錄輸入修改").Cells(32, 3)
Sheets("明細").Cells(M, 58) = Sheets("加油紀錄輸入修改").Cells(33, 2)
Sheets("明細").Cells(M, 59) = Sheets("加油紀錄輸入修改").Cells(33, 3)
Sheets("明細").Cells(M, 60) = Sheets("加油紀錄輸入修改").Cells(34, 2)
Sheets("明細").Cells(M, 61) = Sheets("加油紀錄輸入修改").Cells(34, 3)
Sheets("明細").Cells(M, 62) = Sheets("加油紀錄輸入修改").Cells(35, 2)
Sheets("明細").Cells(M, 63) = Sheets("加油紀錄輸入修改").Cells(35, 3)
Sheets("明細").Cells(M, 64) = Sheets("加油紀錄輸入修改").Cells(36, 2)
Sheets("明細").Cells(M, 65) = Sheets("加油紀錄輸入修改").Cells(36, 3)
Sheets("明細").Cells(M, 66) = Sheets("加油紀錄輸入修改").Cells(37, 2)
Sheets("明細").Cells(M, 67) = Sheets("加油紀錄輸入修改").Cells(37, 3)
Sheets("明細").Cells(M, 68) = Sheets("加油紀錄輸入修改").Cells(38, 2)
Sheets("明細").Cells(M, 69) = Sheets("加油紀錄輸入修改").Cells(39, 2)
Sheets("明細").Cells(M, 70) = Sheets("加油紀錄輸入修改").Cells(39, 3)
Sheets("明細").Cells(M, 71) = Sheets("加油紀錄輸入修改").Cells(40, 2)
Sheets("明細").Cells(M, 72) = Sheets("加油紀錄輸入修改").Cells(41, 2)
Sheets("明細").Cells(M, 74) = Sheets("加油紀錄輸入修改").Cells(42, 2)
Sheets("明細").Cells(M, 75) = Sheets("加油紀錄輸入修改").Cells(43, 2)
Sheets("明細").Cells(M, 76) = Sheets("加油紀錄輸入修改").Cells(44, 2)
Sheets("明細").Cells(M, 77) = Sheets("加油紀錄輸入修改").Cells(45, 2)
Sheets("明細").Cells(M, 87) = Sheets("加油紀錄輸入修改").Cells(55, 2)
Sheets("明細").Cells(M, 88) = Sheets("加油紀錄輸入修改").Cells(56, 2)
Sheets("明細").Cells(M, 89) = Sheets("加油紀錄輸入修改").Cells(57, 2)
Sheets("明細").Cells(M, 90) = Sheets("加油紀錄輸入修改").Cells(58, 2)
Sheets("明細").Cells(M, 91) = Sheets("加油紀錄輸入修改").Cells(59, 2)
      y = y + 1
      End If
      Next
   ' 插入註解()
Set A_1 = [加油紀錄輸入修改!A36]
Set A_3 = [加油紀錄輸入修改!A38]
Set AAA = [加油紀錄輸入修改!A:A]
Set A_2 = [機油總計!a1]
Set A_4 = [機油總計!a1]
For i = 1 To A_2(65536, 1).End(xlUp).Row
If WorksheetFunction.CountIf(AAA, A_2(i, 1)) > 0 Then
y = WorksheetFunction.Match(A_2(i, 1), AAA)
A_2(i, 36).NoteText A_1(y, 2) '註解
End If
Next i

For j = 1 To A_4(65536, 1).End(xlUp).Row
If WorksheetFunction.CountIf(AAA, A_4(j, 1)) > 0 Then
y = WorksheetFunction.Match(A_4(j, 1), AAA)
A_4(j, 37).NoteText A_3(y, 2)
End If
Next j

End Sub

作者: lpk187 時間: 2016-3-11 10:09

回復 1# caimy

可否上傳檔案？

作者: jackyq 時間: 2016-3-11 10:22

可能這樣就可以
For M = 2 To X
If Sheets("明細").Cells(M, 1) = Sheets("加油紀錄輸入修改").Cells(3, 2) Then
      For c2 = 2 To 90 Step 2
            Sheets("明細").Cells(M, c2).Resize(, 2) = Sheets("加油紀錄輸入修改").Cells(c2 / 2 + 4,  2).Resize(, 2)
      Next
y = y + 1
End If
Next

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)