ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¦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

Q_cash.jpeg (84.73 KB)

Q_cash.jpeg

Q_Cash.rar (19.39 KB)

Q_Cash.rar (18.79 KB)

Q_Cash.rar (18.79 KB)

HERO

¦^´_ 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 ªº»¡©ú




·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  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³\¤[)
HERO

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

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
HERO

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ±o²z­nÄǤH¡A²zª½­n®ð©M¡C
ªð¦^¦Cªí ¤W¤@¥DÃD