[問題] 使用者自訂形態尚未定義

看板Office作者greenstars (綠星&喵喵)時間12年前 (2013/07/18 15:33)推噓0(0推 0噓 1→)

留言1則, 1人參與討論串1/1

軟體: excel 2003 vba 寫了一個小巨集在excel內巨集使用都沒有問題但是完成後只要在空白欄位輸入文字按enter 就會出現使用者自訂形態尚未定義如下圖 http://greenstars.pixnet.net/album/photo/128071736 請問有高手知道原因嗎? 巨集程式複製貼上如下 Private Sub CommandButton1_Click() ' 更新文件 ' 清除欄位 Sheets("DOC").Select Sheets("DOC").Range("A2:G65536").Clear Dim i&, y As Integer ' COPY A:C 列 Sheets("SUM").Select Range("A3:C3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("DOC").Select Sheets("DOC").Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues ' COPY E 列 Sheets("SUM").Select Range("E3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("DOC").Select Sheets("DOC").Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues ' 更新美金 y = Sheets("DOC").[A65536].End(xlUp).Row For i = 2 To y ' 更新 USD 小計 Sheets("DOC").Cells(i, 5).FormulaR1C1 = "=RC[-1] * RC[-2]" ' 更新匯率 Sheets("DOC").Cells(i, 6) = Sheets("SUM").Cells(2, 13) ' 更新台幣小計 Sheets("DOC").Cells(i, 7).FormulaR1C1 = "=Round(RC[-1] * RC[-2],0)" Next i '合計在最下方 Sheets("DOC").Cells(y + 1, 3).Formula = "=sum(DOC!C2:C" & y & ")" Sheets("DOC").Cells(y + 1, 5).Formula = "=sum(DOC!E2:E" & y & ")" Sheets("DOC").Cells(y + 1, 7).Formula = "=sum(DOC!G2:G" & y & ")" Sheets("DOC").Cells(y + 1, 2) = "加總" End Sub Private Sub CommandButton2_Click() ' 更新報關資料 ' 清除欄位 Sheets("CUS").Select Sheets("CUS").Range("A2:G65536").Clear Dim i&, y As Integer Sheets("CUS").Select Sheets("CUS").Columns("A:D").Clear ' COPY I 列 Sheets("SUM").Select Range("I2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("CUS").Select Sheets("CUS").Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues ' COPY L 列 Sheets("SUM").Select Range("L2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("CUS").Select Sheets("CUS").Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues ' COPY JK 列 Sheets("SUM").Select Range("J2:K2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("CUS").Select Sheets("CUS").Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues ' 更新美金 y = Sheets("CUS").[A65536].End(xlUp).Row For i = 2 To y ' 更新 USD 小計 Sheets("CUS").Cells(i, 5).FormulaR1C1 = "=RC[-1] * RC[-2]" ' 更新匯率 Sheets("CUS").Cells(i, 6) = Sheets("SUM").Cells(2, 13) ' 更新台幣小計 Sheets("CUS").Cells(i, 7).FormulaR1C1 = "=Round(RC[-1] * RC[-2],0)" Next i '合計在最下方 Sheets("CUS").Cells(y + 1, 3).Formula = "=sum(CUS!C2:C" & y & ")" Sheets("CUS").Cells(y + 1, 5).Formula = "=sum(CUS!E2:E" & y & ")" Sheets("CUS").Cells(y + 1, 7).Formula = "=sum(CUS!G2:G" & y & ")" Sheets("CUS").Cells(y + 1, 2) = "加總" End Sub Sub CommandButton3_Click() ' 合併同項清除 A C 欄 ' 建立兩份列印表格 Sheets("print").Select Sheets("print").Range("A2:G65536").Clear ' 更新樞紐分析表 Sheets("CAL").Select Sheets("CAL").Columns("H:I").Clear ActiveSheet.PivotTables("樞紐分析表3").PivotCache.Refresh ' COPY樞紐分析表到隔壁HI欄 Sheets("CAL").Select Sheets("CAL").Range("A4").Select Sheets("CAL").Range(Selection, Selection.End(xlToRight)).Select Sheets("CAL").Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("CAL").Range("H1").Select ActiveSheet.Paste ' COPY H欄TO PRINT Sheets("CAL").Range("H2").Select Sheets("CAL").Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("print").Select Sheets("print").Range("A2").Select ActiveSheet.Paste Dim i&, y As Integer y = Sheets("print").[A65536].End(xlUp).Row ' 讀取後面欄位 y=總計更新至y-1 For i = 2 To y - 1 '更新 desp Sheets("print").Cells(i, 2).FormulaR1C1 = "=VLookup(RC[-1], DATA!C[1]:C[2], 2, 0)" ' 更新qty Sheets("print").Cells(i, 3).FormulaR1C1 = "=VLookup(RC[-2], CAL!C[5]:C[6], 2, 0)" ' 更新cif Sheets("print").Cells(i, 4).FormulaR1C1 = "=VLookup(RC[-3], CUS!C[-3]:C[0], 4, 0)" ' 更新 USD 小計 Sheets("print").Cells(i, 5).FormulaR1C1 = "=RC[-1] * RC[-2]" ' 更新匯率 Sheets("print").Cells(i, 6) = Sheets("SUM").Cells(2, 13) ' 更新台幣小計 Sheets("print").Cells(i, 7).FormulaR1C1 = "=Round(RC[-1] * RC[-2],0)" Next i '加總在最下列....................... Sheets("print").Cells(y, 3).Formula = "=sum(print!C2:C" & y - 1 & ")" Sheets("print").Cells(y, 5).Formula = "=sum(print!E2:E" & y - 1 & ")" Sheets("print").Cells(y, 7).Formula = "=sum(print!G2:G" & y - 1 & ")" '複製列印兩分 Sheets("print").Range("A1").Select Sheets("print").Range(Selection, Selection.End(xlToRight)).Select Sheets("print").Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("print").Cells.Find(What:="總計").Activate ' 向下移動2格並貼上 ActiveCell.Offset(2, 0).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Private Sub CommandButton4_Click() ' 更新總表報關 Sheets("SUM").Select Dim i&, y As Integer Dim ItemMame As String y = [A65536].End(xlUp).Row For i = 3 To y Sheets("SUM").Cells(i, 9).FormulaR1C1 = "=VLookup(RC[-7], DATA!C[-7]:C[-6], 2, 0)" Sheets("SUM").Cells(i, 12).FormulaR1C1 = "=VLookup(RC[-3], DATA!C[-9]:C[-8], 2, 0)" Next i End Sub Private Sub CommandButton5_Click() Sheets("SUM").Select Sheets("SUM").Range("A3:L65536").Clear End Sub -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 114.33.43.99