各位大大,
小弟寫了1個簡單的VBA自用, 不過有問題解決不了:'(
問題1:
如果將以下這行
my_journal.Cells(i, 7) = my_voucher.Cells(Voucher_First_line, 3) + " " + my_voucher.Cells(Voucher_First_line, 4)
改成
my_journal.Cells(i, 7) = my_voucher.Cells(Voucher_First_line, 3) + " " + my_voucher.Cells(Voucher_First_line, 5)
或
my_journal.Cells(i, 7) = my_voucher.Cells(Voucher_First_line, 3) + " " + my_voucher.Cells(Voucher_First_line, 4)+ " " + my_voucher.Cells(Voucher_First_line, 5)
就出錯
問題2:
如果在Print那張sheet的 (9,1), (9,2)裡填入 1~3, 怎樣寫個VBA由Journal那張sheet的找尋相關資料, 然後逐張列印出來?
謝了
原Code以下
Sub add_data()
Dim count_num As Integer, s, t As Integer
Dim LastRow As Long
Set my_voucher = Worksheets("Voucher")
Set my_journal = Worksheets("Journal")
With my_voucher
.Activate
s = .Cells(Rows.Count, 1).End(xlUp).Row 'Voucher第1個column 由下向上的最後一行
End With
With my_journal
.Activate
t = .Cells(Rows.Count, 1).End(xlUp).Row 'Journal第1個column 由下向上的最後一行
End With
Journal_First_line = t + 1 'Journal第1個column 最後一行的下一行
count_num = s - 9 '總筆數=第1個column 最後一行減標題列行
Voucher_First_line = 10 'Voucher第1行是第10行
For i = Journal_First_line To Journal_First_line + count_num - 1 'Journal的第1行空格 至 Journal的第1行空格+voucher總行數-1
my_journal.Cells(i, 1) = my_voucher.Cells(4, 7) 'Voucher No
my_journal.Cells(i, 2) = my_voucher.Cells(Voucher_First_line, 1) 'Account Code
my_journal.Cells(i, 3) = my_voucher.Cells(Voucher_First_line, 2) 'Account Name
my_journal.Cells(i, 4) = my_voucher.Cells(5, 7) 'Date
my_journal.Cells(i, 5) = my_voucher.Cells(6, 7) 'Cheque No
my_journal.Cells(i, 6) = my_voucher.Cells(7, 7) 'Payer Name
my_journal.Cells(i, 7) = my_voucher.Cells(Voucher_First_line, 3) + " " + my_voucher.Cells(Voucher_First_line, 4) '合併Particular
my_journal.Cells(i, 8) = my_voucher.Cells(Voucher_First_line, 6) 'Dr
my_journal.Cells(i, 9) = my_voucher.Cells(Voucher_First_line, 7) 'Cr
Voucher_First_line = Voucher_First_line + 1
Next i
With my_voucher
.Activate
Cells(s + 1, 5).Value = "總數"
Cells(s + 1, 6).Formula = "=SUM(F10:F" & s + 1 - 1 & ")" '最後1行向右第4空位
Cells(s + 1, 7).Formula = "=SUM(G10:G" & s + 1 - 1 & ")" '最後1行向右第5空位
End With
End Sub |
發表於 2015-3-17 15:05
| 
