- 帖子
- 582
- 主題
- 75
- 精華
- 0
- 積分
- 682
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- office 2010
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-10-30
- 最後登錄
- 2026-4-13
|
本帖最後由 198188 於 2023-11-5 09:59 編輯
不懂得如何計算
198188 發表於 2023-11-4 21:13 
求簡法，加上不同語言的excel, 下面中文語法，在其他語言版本的excel會出現error, 有無解決方式？
Sub Summary()
Sheets.Add After:=ActiveSheet
Sheets("工作表1").Select
Sheets("工作表1").Name = "Summary"
Range("D30").Select
Sheets("Part List").Select
Columns("A:A").Select
Selection.Copy
Sheets("Summary").Select
Range("K1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Part List").Select
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Part List").Select
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("M1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Frame List").Select
Range("B1:F1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("N1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:R").Select
Columns("K:R").EntireColumn.AutoFit
Range("N2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],'Frame List'!C[-13]:C[-8],2,0),0)*M2"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],'Frame List'!C[-14]:C[-9],3,0),0)*M2"
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-4],'Frame List'!C[-15]:C[-10],4,0),0)*M2"
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],'Frame List'!C[-16]:C[-11],5,0),0)*M2"
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],'Frame List'!C[-17]:C[-12],6,0),0)*M2"
Range("N2:R2").Select
Selection.AutoFill Destination:=Range("N2:R306")
Range("N2:R306").Select
Columns("K:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Summary!R1C11:R1048576C18", Version:=6).CreatePivotTable TableDestination _
:="Summary!R1C1", TableName:="枷だ猂1", DefaultVersion:=6
Sheets("Summary").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("枷だ猂1").PivotFields("PART-NO")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("樞紐分析表1").AddDataField ActiveSheet.PivotTables("樞紐分析表1" _
).PivotFields("02F-09F"), "加總 - 02F-09F", xlSum
ActiveSheet.PivotTables("樞紐分析表1").AddDataField ActiveSheet.PivotTables("樞紐分析表1" _
).PivotFields("10F-17F"), "加總 - 10F-17F", xlSum
ActiveSheet.PivotTables("樞紐分析表1").AddDataField ActiveSheet.PivotTables("樞紐分析表1" _
).PivotFields("18F-25F"), "加總- 18F-25F", xlSum
ActiveSheet.PivotTables("樞紐分析表1").AddDataField ActiveSheet.PivotTables("樞紐分析表1" _
).PivotFields("26F-33F"), "加總 - 26F-33F", xlSum
ActiveSheet.PivotTables("樞紐分析表1").AddDataField ActiveSheet.PivotTables("樞紐分析表1" _
).PivotFields("34F-42F"), "加總 - 34F-42F", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("K:R").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveWindow.ScrollColumn = 1
Range("A1").Select
End Sub |
|
發表於 2023-11-4 16:34
| 
