作者people008 (瞎)
看板Visual_Basic
標題[VBA ] excel中的ComboBox跟Do While Cells
時間Wed May 30 11:56:04 2007
我該怎麼讓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