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

VBA ¸ê®Æ·j´M°ÝÃD

¦^´_ 50# Qin


1) ¦pªG¥u¥Î "xU.AutoFilter Field:=3, Criteria1:=">=" & Ur1(3) " ³o¤W¥b¥y»yªk,
¦b·j´M¹Lµ{¤¤, ¹ï¨ä¥L¸ê®Æ·|¤£·|¦³¼vÅT. (¦p: ¸ê®Æ·j´M¥X¨Ó¤£§¹¾ã©Î·j´M³t«×½wºCµ¥°ÝÃD.)
¡Ä¡Ä¥u°w¹ï¤é´Á¿z¿ï¡A¤£·|¼vÅT¨ä¥¦Äæ¦ì

2) §Ú¥Î( .xls OR .xlsx) ¦@40¸Uµ§¸ê®Æ·j´M®É, ¤j·§­nªá30¬íªº®É¶¡, ½Ð°ÝÁÙ¥i¥H¥[³t¶Ü?
¡Ä¡Ä§ï¥ÎARRAY©Î³\¥i¥H§Ö¨Ç¡A¦ý¥¼¹ê´ú¡AµLªk½T©w

3) ¦b½s¸¹·j´MÄæ¦ì, ¨Ò¦p½s¸¹¬O " 20000350"  "11005710"  "10003210" ¦Ó§Ú¥u»ÝÁä¤J " 2*350 " ©Î " 11*5710"... ¤]¥i¥H§â¸ê®Æ·j¥X¨Ó.
¡Ä¡Ä½s¸¹¬O¡e¼Æ­È¡f¡A¡e¿z¿ï¡fµLªk¥Î¤å¦r¤ñ¹ï

TOP

¦^´_ 50# Qin


¸Õ¸Õ¬Ý§a:
SearchData03.rar (56.23 KB)

TOP

¦^´_ 52# ­ã´£³¡ªL
     
     ­ã¤j, ³o¬q»yªk®Çªºªþµù¶Ã½X, ³Â·Ð§A¦A±N¥¦¼g¦b¯d¨¥ªO¤W. ÁÂÁÂ!

        ReDim Brr(1 To 60000, 1 To 10)    '­Y¸ê®Æ¡P|¶W¹L6¸Uµ¡±,¦Û¦æ¡±ó¡±ï

TOP

¦^´_ 53# Qin


¦pªG¿z¿ï¥X¨Óªº¸ê®Æ·|¶W¹L6¸Uµ§, ±N60000§ï¬°§ó¤j(¦h¤j? ¦Û¦æ·r°u)

TOP

¦^´_ 54# ­ã´£³¡ªL

        ­ã¤j               
                       
        §Ú¤S·Q¦A½Ð±Ð§A2­Ó°ÝÃD:               
        ­º¥ý»¡²M·¡, ¥Î§Aµ¹ªº "Data" ÀɬO¨S¦³°ÝÃDªº, ¦ý, ¦p¨Ï¥Î§Ú¦Û¤vªº¤åÀɤ~·|¥X²{³o°ÝÃD.               
                       
1)        ¥H"«~¦W·j´M"               
        ´N·|¥X²{:  If Ur1(j) <> "" Then If Not Arr(i, Ur2(j)) Like Ur1(j) Then GoTo 102               
                       
        ¥H"½s¸¹·j´M"               
        ´N·|¥X²{:  dd = Arr(i, 3)               
                       
        ·Q½Ð±Ð¬O§_¤S¬O¦]¬°"¤é´Á®æ¦¡"ªº½t¬G.               
        ¦pªG"¬O", ¥i§_½Ð§A¦AÀ°§Ú­×§ï, ¥u¥H"From Date" ·j´M´N¥i¥H¤F               
                       
2)        ­ã¤j, §A¯uªº¦³¨D¥²À³®@! §Ú­n¨D¥H" * " ¥H¬P¦rÁä¨Ó°µ·j´M, §A¤]¹F¦¨¤F§Ú­n¨D.               
        ¦ý¬O, ÁÙ¦³¤@­Ó¤p¤pªº°ÝÃD,´N¬O¬°¦ó"½s¸¹ , «~¦W©M¼t°Ó"·j´M®É³£¥²»Ý¨Ï¥Î " ¤j¦r¥À" (Caps Lock)               
                       
        ¨Ò:               
        h*1234               
        *french*               
        aa*               
        "§ä¤£¨ì²Å¦Xªº¸ê®Æ"               
                       
        H*1234               
        *French*               
        AA*               
        ·j´Mµ²ªG¨S°ÝÃD               
                       
        ¥i§_­×§ï¦¨"¤j¤p¦r¥À"¬Ò³q¥Î. ÁÂÁÂ!               
                       
Search Data_180930.rar (133.7 KB)

TOP

¦^´_ 55# Qin

Sub Search_Data(Ur1, Ur2)
Dim Sht As Worksheet, Arr, Brr, i&, j%, k%, N&, dd&
Dim Mybook As Workbook, xB As Workbook, xChk%
Call Clear_All
xN = "Data.xls": Set Mybook = ThisWorkbook
On Error Resume Next: Set xB = Workbooks(xN): On Error GoTo 0
If xB Is Nothing Then
   Application.ScreenUpdating = False
   Set xB = Workbooks.Open("C:\Users\Ms Tan\Desktop\Data.xls", , 1, , "1234")
   Mybook.Activate: xChk = 1
End If
'----------------------------
ReDim Brr(1 To 400000, 1 To 10) '­Y¸ê®Æ·|¶W¹L6¸Uµ§,¦Û¦æ§ó§ï
For Each Sht In xB.Sheets
    If LCase(Left(Sht.Name, 4)) <> "data" Then GoTo 101
    Arr = Range(Sht.[J2], Sht.Cells(Rows.Count, 1).End(xlUp))
    For i = 1 To UBound(Arr)
        For j = 0 To 2
            If Ur1(j) <> "" Then If LCase(Arr(i, Ur2(j))) Like LCase(Ur1(j)) = False Then GoTo 102
        Next j
        dd = 0
        If IsDate(Arr(i, 3)) Then dd = Arr(i, 3)
        If dd < Ur1(3) Then GoTo 102
        N = N + 1
        For k = 1 To UBound(Brr, 2): Brr(N, k) = Arr(i, k): Next
102: Next i
101: Next
If xChk = 1 Then xB.Close 0
'----------------------------
If N = 0 Then MsgBox "§ä¤£¨ì²Å¦X¸ê®Æ!": Exit Sub
With [A8:J8].Resize(N)
     .Value = Brr
     .Sort Key1:=.Item(3), Order1:=xlDescending, Header:=xlNo
     [A4:J5].Copy
     .Cells.PasteSpecial Paste:=xlFormats
End With
[A6].Select
End Sub

Sub Clear_All()
With Sheets("Search")
     If .FilterMode Then .ShowAllData
     With .UsedRange.Offset(7, 0)
          .ClearContents
          .Interior.ColorIndex = xlNone
     End With
     .[A1,C1:C3].Interior.ColorIndex = 15
     .[B1:B3].Interior.ColorIndex = 35
     .[A6].Select
End With
End Sub

Sent_01.rar (135.54 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-10-1 11:02 ½s¿è

¦^´_ 55# Qin


    ¸Õ¸Õ¬Ý

Search Data_04.zip (122.83 KB)

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

TOP

¦^´_ 57# GBKEE

ÁÂÁÂ! §Ú·|°Ñ¦Ò..

TOP

¥»©«³Ì«á¥Ñ Qin ©ó 2018-10-3 23:17 ½s¿è

¦^´_ 56# ­ã´£³¡ªL

        ­ã¤j
       
        §Ú¤S¹J¨ì°ÝÃD¤F¡K.
       
1)        ³o¦¸¬O"«~¦W·j´M"°ÝÃD, ·í¦bB2¿é¤J·j´M±ø¥ó«á,´N·|¥X²{
         If Ur1(j) <> "" Then If LCase(Arr(i, Ur2(j))) Like LCase(Ur1(j)) = False Then GoTo 102
       
        ¦]¬°¤§«e¨ü¨ì"¤é´Á®æ¦¡"ªº§xÂZ, ¥H¬°³o¦¸¤S¬O¤°»ò"¤å¦r®æ¦¡"
        ´N¶]¨ìVBE ½s¿è¾¹ Tools-> Options -> Editor Format -> Font °µ­×§ï
        µL·N¤¤µo²{¤§«eªº¶Ã½X²{¶H, ¥i¥H¦b¦¹Àò±o¸Ñ¨M.
       
2)        ¦b B1, B2,B3 Äæ, ¬O¥H"Âù¼°Ä²°Ê"¨Ó·j´M¸ê®Æ, ¥i§_§ï¦¨¿é ¤J·j´M±ø¥ó«á,«ö Enter ´N¥iÀò±o·j´Mµ²ªG.
        ¦]¬°·Pı¤ñ¸û¦n¨Ï¥Î.¦Ü©ó¦h±ø¥ó·j´M´N«O¯d­ì¥»ªº¤è¦¡.
        ­ã¤j, ¦pªG³o­Ó­×§ï·|¦³"²o¤@¾v¦Ó°Ê¥þ¨­"ªº¤j´T«×§ó§ï, ¨º´N§K¤F§a!
       
        ÁÂÁÂ!!
SearchData_181003.rar (428.06 KB)
¶Ã½X.png
2018-10-3 23:04
Error_20181003.png
2018-10-3 23:04

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-10-4 10:57 ½s¿è

¦^´_ 59# Qin

«~¦W·j´M·|¥X²{¿ù»~:
__¬Ý[data]ªíªº G2703 ¬°#N/A,

For j = 0 To 2
    If IsError(Arr(i, Ur2(j))) Then GoTo 102 '¦b³o¦ì¸m¥[³o¤@¦æ
    If Ur1(j) <> "" Then If LCase(Arr(i, Ur2(j))) Like LCase(Ur1(j)) = False Then GoTo 102
Next j


¦Ü©ó·Q[Âù«ö¥ªÁä]§ï¦¨[ENTER]°õ¦æ, ¤£«Øij³o¼Ë°µ,
CHANGEIJµo, ¨C§ï¤@¦¸§Y°õ¦æ¤@¦¸, ¤£¤ÓÀô«O,
¿é¤J¨Ã½T©w­n·j´M±ø¥óµL»~, ¦A°õ¦æµ{¦¡, ¤~¬O³Ì§´·í, ®t¤£¤F¦h¤Ö®É¶¡,
¸ê®Æ³B²zªÌ, ¦³®É¤£­n¶û³Â·Ð~~

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD