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

­×§ï¥N½X

­×§ï¥N½X

¥»©«³Ì«á¥Ñ vinejason ©ó 2014-6-6 12:25 ½s¿è

¥­¦w
¦³4­ÓSheet ¤À§O¬O¾P³f±b³æ »P ¾P³f©ú²Ó »P ®Ñ¥Ø »P «È¤á¸ê®Æ
¤U¦C¥N½X¥i¿é¤J¾P³f©ú²Óªº½s¸¹ ¿ï¾Ü¦C¦L
¨Ò¦p¿é¤J  s10305101~s10305110  
¥i§ì¨ú¸ê®Æ¶i¦æ¦C¦L¦¹¤Q±i¤£¦P¦U¤áªº¾P³f³æ
¥u¬O°õ¦æ³t«×¬Û·í½wºC

Public Sub prtbill(bgnum, ndnum)
Application.StatusBar = "³øªí¦C¦L°õ¦æ¤¤@@@@@·q½Ðµy­Ô!"
Sheet1.Cells.AutoFilter
Dim rng As Range
Sheet2.Activate '¾P³f±b³æ
Sheets("¾P³f±b³æ").Unprotect  'Á׶}«OÅ@
Set rng = Union(Range("B9:D32"), Range("F9:F32"), Range("H9:H32"), Range("C4:D7"), Range("C3"), Range("G5:G6"))
rng.ClearContents
fmidx = CLng(bgnum)
toidx = CLng(ndnum)
With Sheet3 '¾P³f²M³æ
For i = fmidx To toidx
    rr = 2
    nn = 9
    Do Until .Cells(rr, "c") = Empty
        If .Cells(rr, 3).Value = i Then
            Cells(3, "g") = .Cells(rr, 3).Text
            Cells(3, "c") = .Cells(rr, 1)
            Cells(4, "g") = .Cells(rr, 4)
            Cells(7, "g") = .Cells(rr, 9)
            Cells(nn, "b") = .Cells(rr, "e")
            Cells(nn, "d") = .Cells(rr, "f")
            Cells(nn, "f") = .Cells(rr, "g")
            Cells(nn, "h") = .Cells(rr, "l")
           
  With Sheets("«È¤á¸ê®Æ")
               Set F1 = .Columns(1).Find([C3])
               [c4] = .Cells(F1.Row, 2)
               [c5] = .Cells(F1.Row, 7)
               [c6] = .Cells(F1.Row, 6)
               [G5] = .Cells(F1.Row, 4)
               [G6] = .Cells(F1.Row, 3)
          End With
            nn = nn + 1
            If nn = 33 Then
                ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
                rng.ClearContents
                nn = 9
            End If
        End If
        rr = rr + 1
    Loop
    If nn > 9 Then ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    rng.ClearContents
Next i
End With
    ActiveSheet.Protect '«ì´_«OÅ@
    Set BillRange = Worksheets("¾P³f²M³æ").Range("C1:C65535")  '³f³æ½s¸¹»¼¼W
    n = Application.WorksheetFunction.Max(BillRange)
    Sheets("¾P³f±b³æ").Select
    Range("G3").Select
    ActiveCell.FormulaR1C1 = n + 1
   
    Sheets("¾P³f±b³æ").Select
    Range("C3").Select
    'Range("R9:R32").ClearContents
   
    Set x = Cells.Find("xx", lookat:=xlPart) 'ÁÙ­ì"´M§ä®Ø"¤£¤Ä¿ïª¬ºA
   Set rng = Nothing 'ª«¥óÄÀ©ñ
    Application.StatusBar = False
End Sub
µL¤îºÉªº§V¤O¤W¶i

¦^´_ 1# vinejason

ªþÀɤñ¸û®e©ö¬ÝÀ´

TOP

¦^´_ 2# stillfish00

ÁÙ¨SÅv­­¤W¶ÇÀÉ®× !
¸U¤À·PÁ±z
µL¤îºÉªº§V¤O¤W¶i

TOP

¦^´_  stillfish00

ÁÙ¨SÅv­­¤W¶ÇÀÉ®× !
¸U¤À·PÁ±z
vinejason µoªí©ó 2014-6-6 22:25


½Ð¬ÝªþÀÉ
ÁÂÁÂExcel¹F¤H­Ì ¦C¦L.rar (86.56 KB)
µL¤îºÉªº§V¤O¤W¶i

TOP

¦^´_ 1# vinejason
1. µ{§Ç«e«á¥[¤WApplication.ScreenUpdating=False©MApplication.ScreenUpdating=True¬Ý¬Ý
2. µ{§Ç¤¤¹ï"¾P³f±b³æ"ªºÀx¦s®æ¶ñ­È, ·|IJµo¸Ó¤u§@ªí¤ºWorksheet_Change¨Æ¥ó¤¤§A¼gªºµ{¦¡½X
     ©Ò¥H¦h¶]«Ü¦hcode , ¬Ý¦³¨S¦³¥²­nIJµo , ­Y¤£·QIJµo¡A¦P1.¥i§Q¥ÎApplication.EnableEvents

TOP

¦^´_ 5# stillfish00

stillfish00 ¥­¦w
ÁÂÁ±zªº«Øij §Ú·|¸Õ¸Õ¬Ý
§ó·PÁ±z¦Ê¦£¤§¤¤ªº¦^À³
¯¬ºÖ±z
¦b©Ò¦æªº¹D¸ô¤W¥Rº¡´¼¼z»P¤O¶q
µL¤îºÉªº§V¤O¤W¶i

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD