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

[µo°Ý] ¬d¸ß¤é´Á±a¥X¦¹¤é´Áªº¨Cµ§¸ê®Æ

[µo°Ý] ¬d¸ß¤é´Á±a¥X¦¹¤é´Áªº¨Cµ§¸ê®Æ

¥»©«³Ì«á¥Ñ chyong911 ©ó 2019-4-25 15:33 ½s¿è

¦U¦ì«e½ú¦n¡A¦]¬°¦Û¾ÇVBA¡A·§©À¨S¦³«Ü¯Â¼ô¡A°µ¨ì¤@¥b¥dÃö¤F¡A¸÷¨D¨ó§UQQ
·Q­n¼g¤@½g¥i¥H¥Î"¤ä²¼¨ì´Á¤é"¨Ó±q¸ê®Æ®w«õ¥X¦¹¨ì´Á¤éªº¨Cµ§¤ä²¼¸¹(¤£­n­«½Æ)¤Î¦¹¤ä²¼¸¹ªº¶Ç²¼³æ¸¹¡B§I²{¤é¡B¤½¥q§O¡A¦ý¥Ø«e§Ú¼gªºVBA¥Ø«e¥u¯à¼g¥X¤@µ§Orz


§Ú«ä¦Ò¥XªºÅÞ¿è¥Ü·N¹Ï¦p¤U(¦ý¥Ø«e¼g¤£¥X¨Ó)

¥Ü·N¹Ï

2019-04-25_145017.jpg
2019-4-25 14:50


¼gªºVBA¬°¡G
PS³o¤@¬q¨C¦¸«ö¶s¤U¥h³£¶]¶W¤[¡A¬O¤£¬O§Ú¼g±o¤Ó¤£ºë²¤FQQ¦³§ó¦nªº¤è¦¡¶Ü?
¤ä²¼¬d¸ß.rar (129.42 KB)

Private Sub ¬d¸ß_Click()
'Ãö³¬¿Ã¹õ¸õÅD
    Application.ScreenUpdating = False
    Sheets("²¼¾Úñ¦¬³æ").Select
   
    '¶×¥XÄæ¦ì¨Ï¥Î
    Dim NumberColumn, DayColumn, SummonsColumn, BusinessColumn As Integer '¤ä²¼¸¹ '¨ì´Á¤é'¶Ç²¼³æ¸¹'¤½¥q§O
    X = Sheets("²¼¾Úñ¦¬³æ").Cells(9, "W") '¿é¤J¨ì´Á¤é
   
    '¹w³]Äæ¦ì¦ì¤l
    NumberColumn = 6
    DayColumn = 6
    BusinessColumn = 6
    SummonsColumn = 6
   
   
    '²MªÅ¬d¸ßµ²ªG
    ClearFunction
   
    '°õ¦æ¬d¸ß
For i = 3 To 1000 'data¤W­­1000µ§

        'InStr·|¶Ç¦^ Variant (Long)¡A«ü©w¥t¤@­Ó¦r¦ê¤º¬Y­Ó¦r¦êªº²Ä¤@­Ó¥X²{¦ì¸m¡C
        'InStr([ start ], string1, string2, [ compare ])
        'If Sheets("¨t²Î³]©w").Cells(i, "D").Contains(x) Then
        
    If InStr(Sheets("À³¥I²¼¾Údata").Cells(i, "C"), X) > 0 Then '§ä¨ì²Ä¤@®æ¸ê®Æ
           'Value
           r = Sheets("À³¥I²¼¾Údata").Cells(i, "AC").Row
           v = Sheets("À³¥I²¼¾Údata").Cells(i, "AC").Value
           For p = 0 To v - 1
            Sheets("²¼¾Úñ¦¬³æ").Cells(SummonsColumn, "X") = Sheets("À³¥I²¼¾Údata").Cells(i, "C") '¶Ç²¼³æ¸¹
            Sheets("²¼¾Úñ¦¬³æ").Cells(NumberColumn, "Y") = Sheets("À³¥I²¼¾Údata").Cells(i, "V") '¤ä²¼¸¹
            Sheets("²¼¾Úñ¦¬³æ").Cells(DayColumn, "z") = Sheets("À³¥I²¼¾Údata").Cells(i, "Y") '¨ì´Á¤é
            Sheets("²¼¾Úñ¦¬³æ").Cells(BusinessColumn, "aa") = Sheets("À³¥I²¼¾Údata").Cells(i, "D") '¤½¥q§O
            Next
            p = p + 1
     End If
Next
   
    NumberColumn = NumberColumn + 1
    DayColumn = DayColumn + 1
    SummonsColumn = SummonsColumn + 1
    BusinessColumn = BusinessColumn + 1
   
    Sheets("²¼¾Úñ¦¬³æ").Select
End Sub
------------------------------------------------------------------------------------
Private Sub ²M°£¬d¸ß_Click()
'Ãö³¬¿Ã¹õ¸õÅD
    Application.ScreenUpdating = False
    Sheets("²¼¾Úñ¦¬³æ").Range("X6:AA17").ClearContents
    Sheets("²¼¾Úñ¦¬³æ").Cells(6, "W").ClearContents
    Sheets("²¼¾Úñ¦¬³æ").Cells(9, "W").ClearContents
   
End Sub
--------------------------------------------------------------------------------------
Private Sub ClearFunction()
    'Ãö³¬¿Ã¹õ¸õÅD
    Application.ScreenUpdating = False
    Sheets("²¼¾Úñ¦¬³æ").Range(Cells(6, 24), Cells(12, 29)).ClearContents

End Sub



¥H¤W³Â·Ðª©¤Wªº¦U¦ì«e½ú«ü±Ð¤FOrz

¦^´_ 1# chyong911
¸Õ¸Õ¬Ý
  1. Private Sub ¬d¸ß_Click()
  2.     Dim Sh(1 To 2) As Worksheet, Rng(1 To 2) As Range, P As Integer, i As Integer
  3.     Dim Ar()
  4.    
  5.     Application.ScreenUpdating = False ''Ãö³¬¿Ã¹õ¸õÅD
  6.     Set Sh(1) = Sheets("²¼¾Úñ¦¬³æ")
  7.     Set Sh(2) = Sheets("À³¥I²¼¾Údata")
  8.    
  9.     Set Rng(1) = Sh(1).Range("W6") '¨ì´Á¤é
  10.     Set Rng(2) = Sh(2).Range("C3") '®w¶Ç²¼³æ¸¹
  11.    
  12.     If Rng(2) = "" And Rng(2).End(xlDown).Row = Rows.Count Then
  13.         MsgBox "À³¥I¸ê®Æ ¤¤  ¨S¦³ ®w¶Ç²¼³æ¸¹"
  14.         Exit Sub
  15.     End If
  16.     ClearFunction ''²MªÅ¬d¸ßµ²ªG
  17.    
  18.     '°õ¦æ¬d¸ß
  19.     P = 1
  20.     Ar = Array("C", "V", "Y", "D") 'À³¥I²¼¾Údata ­n¾É¤JªºÄæ¦ì
  21.     Do While Rng(2) <> ""      '®w¶Ç²¼³æ¸¹¤£¬°ªÅ­È
  22.         If InStr(Rng(2), Rng(1)) Then '§ä¨ì¿é¤J¨ì´Á¤é
  23.             For i = 1 To 4    'X,Y,Z,AA Äæ¦ì
  24.                 Rng(1).Cells(P, i + 1) = Sh(2).Cells(Rng(2).Row, Ar(i - 1))
  25.             Next
  26.             P = P + 1    '¬d¸ß¨ì +1 ¦C¦ì
  27.         End If
  28.         Set Rng(2) = Rng(2).Offset(1)  '¤U¤@­Ó®w¶Ç²¼³æ¸¹
  29.     Loop
  30.     Application.ScreenUpdating = True
  31. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD