我的代碼如下
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 2 To [A1048576].End(3).Row
If Cells(i, 1) <> "" Then
If Sheet1.Cells(6, 10) = "US" Then Cells(i, 2) = "USD"
ElseIf Sheet1.Cells(6, 10) = "CA" Then Cells(i, 2) = "CAD"
ElseIf Sheet1.Cells(6, 10) = "MX" Then Cells(i, 2) = "MXN"
ElseIf Sheet1.Cells(6, 10) = "JP" Then Cells(i, 2) = "JPY"
ElseIf Sheet1.Cells(6, 10) = "UK" Then Cells(i, 2) = "GBP"
ElseIf Sheet1.Cells(6, 10) = "DE" Or Sheet1.Cells(6, 10) = "FR" Or Sheet1.Cells(6, 10) = "IT" Or Sheet1.Cells(6, 10) = "ES" Then Cells(i, 2) = "EUR"
End If
Cells(i, 3) = Sheet1.Cells(6, 12)
Cells(i, 4) = Sheet1.Cells(3, 10)
Cells(i, 5) = "EXACT"
If Sheet1.Cells(6, 16) = "展現量" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!H:H)")
ElseIf Cells(6, 16) = "點擊量" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!I:I)")
ElseIf Cells(6, 16) = "CTR" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!J:J)")
ElseIf Cells(6, 16) = "CPC" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!K:K)")
ElseIf Cells(6, 16) = "廣告費" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!L:L)")
ElseIf Cells(6, 16) = "ACoS" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!M:M)")
ElseIf Cells(6, 16) = "RoAS" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!N:N)")
ElseIf Cells(6, 16) = "訂單量" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!S:S)")
ElseIf Cells(6, 16) = "CR" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!R:R)")
ElseIf Cells(6, 16) = "銷售額" Then Cells(i, 6) = Evaluate("SUMPRODUCT((保薦產品投放報告!A:A=A2)*(保薦產品投放報告!C:C=B2)*ISNUMBER(FIND(作業平臺!$L$6,保薦產品投放報告!D:D))*(保薦產品投放報告!F:F=D2)*(保薦產品投放報告!G:G=E2),保薦產品投放報告!U:U)")
End If
Else
Cells(i, 2) = ""
Cells(i, 3) = ""
Cells(i, 4) = ""
Cells(i, 5) = ""
Cells(i, 6) = ""
End If
Next
End Sub
uj5u.com熱心網友回復：
代碼無效了的話，就不能運行了吧。很卡，你的回圈是從頭到了最后的 1M 行，是要不少時間吧。或可用 Worksheet(?).UsedRange 來縮小下需要操作的范圍。uj5u.com熱心網友回復：
卡的問題是的你代碼生成的表里面含有大量的計算公式，單元格中的值改變一下你，計算公式就自動計算。
可以在程式開始時關閉自動計算，在處理結束后再打開自動計算。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/35457.html