[VBA ] excel中的ComboBox跟Do While Cells

看板Visual_Basic作者people008 (瞎)時間18年前 (2007/05/30 11:56)推噓0(0推 0噓 0→)

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

我該怎麼讓ComboBox所選的年度根據資料數值跑出正確數字之外讓科目跟金額還有下個年度都正確 Private Sub ComboBox1_Change() Sheets("其他資料").Select [i1] = ComboBox1.Value Range("i2").Select End Sub Private Sub CommandButton1_Click() Dim Rcol As Integer Dim Rcoll As Integer Dim c As Integer Sheets("其他資料").Activate [i1].Select Selection.Copy Sheets("分錄紀錄").Select Rcoll = 1 Do While Cells(1, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(1, Rcoll).Select ActiveSheet.Paste Sheets("其他資料").Activate Rcol = 1 Do While Cells(4, Rcol) <> "" Rcol = Rcol + 1 Loop Cells(5, Rcol - 1).Select Selection.Copy Sheets("分錄紀錄").Select Rcolll = 1 Do While Cells(2, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(2, Rcoll).Select ActiveSheet.Paste Sheets("其他資料").Activate Rcol = 1 Do While Cells(4, Rcol) <> "" Rcol = Rcol + 1 Loop Cells(6, Rcol - 1).Select Selection.Copy Sheets("分錄紀錄").Select Rcoll = 1 Do While Cells(3, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(3, Rcoll).Select ActiveSheet.Paste Sheets("其他資料").Activate Rcol = 1 Do While Cells(4, Rcol) <> "" Rcol = Rcol + 1 Loop Cells(7, Rcol - 1).Select Selection.Copy Sheets("分錄紀錄").Select Rcoll = 1 Do While Cells(4, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(4, Rcoll).Select ActiveSheet.Paste Sheets("其他資料").Activate Rcol = 1 Do While Cells(4, Rcol) <> "" Rcol = Rcol + 1 Loop Cells(8, Rcol - 1).Select Selection.Copy Sheets("分錄紀錄").Select Rcoll = 1 Do While Cells(5, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(5, Rcoll).Select ActiveSheet.Paste Sheets("其他資料").Activate Rcol = 1 Do While Cells(4, Rcol) <> "" Rcol = Rcol + 1 Loop Cells(9, Rcol - 1).Select Selection.Copy Sheets("分錄紀錄").Select Rcoll = 1 Do While Cells(6, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(6, Rcoll).Select ActiveSheet.Paste Sheets("其他資料").Activate Rcol = 1 Do While Cells(4, Rcol) <> "" Rcol = Rcol + 1 Loop Cells(10, Rcol - 1).Select Selection.Copy Sheets("分錄紀錄").Select Rcoll = 1 Do While Cells(7, Rcoll) <> "" Rcoll = Rcoll + 1 Loop Cells(7, Rcoll).Select ActiveSheet.Paste Sheets("分錄紀錄").Select c = 1 Do While Cells(1, c) <> "" c = c + 1 Loop Cells(4, c).Select Cells(9, c - 1).Value = Val(Cells(4, c - 1)) - Val(Cells(9, c - 2)) Cells(10, c - 1).Value = Val(Cells(5, c - 1)) - Val(Cells(10, c - 2)) Cells(11, c - 1).Value = Val(Cells(6, c - 1)) - Val(Cells(11, c - 2)) Cells(12, c - 1).Value = Val(Cells(7, c - 1)) - Val(Cells(12, c - 2)) [a18] = "所得稅費用" [a19] = "遞延所得稅資產─流動" [a20] = "遞延所得稅資產─非流動" [b20] = "遞延所得稅負債─流動" [b21] = "遞延所得稅負債─非流動" [b22] = "當期應付所得稅" [e22] = "=當期應付所得稅" [d18] = [e20].Value + Cells(9, c - 1).Value + Cells(10, c - 1).Value - Cells(11, c - 1).Value - Cells(12, c - 1).Value [d19] = Cells(12, c - 1).Value [d17] = Cells(11, c - 1).Value [e20] = Cells(10, c - 1).Value [e21] = Cells(9, c - 1).Value End Sub Private Sub CommandButton2_Click() Unload Me End Sub Excel資料: H I J 1 科目 95 96 2 所得稅費用 NT$206,578 NT$311,874 3 遞延所得稅資產─流動 NT$20,655 NT$20,655 4 遞延所得稅資產─非流動 NT$124,757 NT$24,070 5 遞延所得稅負債─流動 NT$175,780 NT$91,460 6 遞延所得稅負債─非流動 NT$34,348 NT$85,921 7 當期應付所得稅 NT$141,862 NT$179,218 問題長了點，麻煩各位高手了 -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 220.135.100.160