[µo°Ý] ±Nvoucherªº¸ê®Æ¼g¤Jjournal¥X¿ù

Hero2013

¦U¦ì¤j¤j,
¤p§Ì¼g¤F1­Ó²³æªºVBA¦Û¥Î, ¤£¹L¦³°ÝÃD¸Ñ¨M¤£¤F:'(
°ÝÃD1:
¦pªG±N¥H¤U³o¦æ
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)
´N¥X¿ù

°ÝÃD2:
¦pªG¦bPrint¨º±isheetªº (9,1), (9,2)¸Ì¶ñ¤J 1~3, «ç¼Ë¼g­ÓVBA¥ÑJournal¨º±isheetªº§ä´M¬ÛÃö¸ê®Æ, µM«á³v±i¦C¦L¥X¨Ó?

Á¤F

­ìCode¥H¤U
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 ¥Ñ¤U¦V¤Wªº³Ì«á¤@¦æ
End With

With my_journal
     .Activate
     t = .Cells(Rows.Count, 1).End(xlUp).Row 'Journal²Ä1­Ócolumn ¥Ñ¤U¦V¤Wªº³Ì«á¤@¦æ
End With


Journal_First_line = t + 1 'Journal²Ä1­Ócolumn ³Ì«á¤@¦æªº¤U¤@¦æ

count_num = s - 9 'Á`µ§¼Æ=²Ä1­Ócolumn ³Ì«á¤@¦æ´î¼ÐÃD¦C¦æ

Voucher_First_line = 10 'Voucher²Ä1¦æ¬O²Ä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) '¦X¨Ö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¦æ¦V¥k²Ä4ªÅ¦ì
   
Cells(s + 1, 7).Formula = "=SUM(G10:G" & s + 1 - 1 & ")" '³Ì«á1¦æ¦V¥k²Ä5ªÅ¦ì

End With

End Sub

GBKEE

¦^´_ 1# Hero2013

1. my_journal.Cells(i, 7) = my_voucher.Cells(Voucher_First_line, 3) + " " + my_voucher.Cells(Voucher_First_line, 4)
   
2. Debug.Print my_voucher.Cells(Voucher_First_line, 3), my_voucher.Cells(Voucher_First_line, 4), my_voucher.Cells(Voucher_First_line, 5)

½Æ»s¥N½X

¦r¦ê + ¦r¦ê ¬O¥i¥Hªº
¦r¦ê &  ¼Æ¦r ¤~¥¿½T  
½Ð¬Û¬ÝVBA ¹Bºâ¤lºK­n ªº»¡©ú

¤U¸ü (31.2 KB)

2015-3-17 16:02

Hero2013

¦^´_  Hero2013 ¦r¦ê + ¦r¦ê ¬O¥i¥Hªº
¦r¦ê &  ¼Æ¦r ¤~¥¿½T  
½Ð¬Û¬ÝVBA ¹Bºâ¤lºK­n ªº»¡©ú
GBKEE µoªí©ó 2015-3-17 16:03

GBKEE¤j¤j, ÁÂÁ§A.

°ÝÃD2:
¦pªG¦bPrint¨º±isheetªº (9,1), (9,2)¸Ì¶ñ¤J 1~3, «ç¼Ë¼g­ÓVBA¥ÑJournal¨º±isheetªº§ä´M¬ÛÃö¸ê®Æ, µM«á³v±i¦C¦L¥X¨Ó? (³o­Ó°ÝÃD¤]§xÂZ¤F³\¤[)

GBKEE

¦^´_ 3# Hero2013
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Ar(), Ay(), Rng As Range, i As Integer, R As Integer
   
4.     With Sheets("print")
   
5.         For i = .Range("G9").Value To .Range("H9").Value
   
6.             With Sheets("Journal")
   
7.                 Set Rng = .Range("A5", .Range("A5").End(xlDown)).Find(i, LOOKAT:=xlWhole)
   
8.             End With
   
9.             If Not Rng Is Nothing Then
   
10.                 Do While Rng = i
    
11.                     R = R + 1
    
12.                     With Rng  
    
13.                         ReDim Preserve Ar(1 To R)
    
14.                         Ar(R) = Array(.Range("B1").Value, .Range("C1").Value, .Range("G1").Value, .Range("H1").Value, .Range("I1").Value)
    
15.                     End With
    
16.                     Set Rng = Rng.Offset(1)
    
17.                 Loop
    
18.                 Ay = Application.Transpose(Ar)
    
19.                 R = R + 1
    
20.                 ReDim Preserve Ar(1 To R)
    
21.                 Ar(R) = Array("", "", "Á` ¼Æ:", Application.Sum(Application.Index(Ay, 4)), Application.Sum(Application.Index(Ay, 5)))
    
22.                 'Dr­É¶µ Application.Index(Ay, 4)
    
23.                 'Cr¶U¶µ Application.Index(Ay, 5)
    
24.                 Ar = Application.Transpose(Application.Transpose(Ar))
    
25.                 .[a10:e10].Resize(R).Value = Ar
    
26.                 .PageSetup.PrintArea = "A3:" & .[a10:e10].Resize(R).Address  '³]©w¦L¦C½d³ò
    
27.                 .PrintOut           '¦L¦C
    
28.                 Erase Ar            '­«·sªì©l¤Æ©T©w¤j¤p°}¦Cªº¤¸¯À¡A¨ÃÄÀ©ñ°ÊºA°}¦CªºÀx¦sªÅ¶¡
    
29.                 .[a10:e10].Resize(R) = ""
    
30.                 R = 0
    
31.             End If
    
32.         Next
    
33.     End With
    
34. End Sub

½Æ»s¥N½X

Hero2013

¦^´_ 4# GBKEE
ÁÂÁÂGBKEE¦Ñ¤j¡A§A¯uªº¤Ó±j¤F¡A·P®¦:)

Hero2013

GBKEE¦Ñ¤j,
¤£¦n·N«ä, §Ú¸Õ¤F¤@¤U, µo²{
Set Rng = .Range("A5", .Range("A5").End(xlDown)).Find(i, LOOKAT:=xlWhole)
¦C¦L²Ä1±i®É·|¤Ö¤F²Ä¤@¦æ
±NA5§ï¦¨A4´N¥i¥H(ÁöµM§Ú¤£©ú¥Õ)
¥t¥~´N¬O "Print"¨º­Ósheetªº E4¦ÜE7¨S¦³±q"Journal"¨º­Ósheet´£¨ú¬ÛÃö¸ê®Æ
¥i¥H§ï¤@¤U¶Ü? ÁÂÁ§A¤F

GBKEE

¦^´_ 6# Hero2013
­×§ï¤@¤U After(°Ñ¼Æ):±q³Ì«á¶}©l·j´M

1. Set Rng = .Range("A5", .Range("A5").End(xlDown)).Find(i, LOOKAT:=xlWhole, After:=.Range("A5").End(xlDown))

½Æ»s¥N½X