作者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