­ã´£³¡ªL

¦^´_ 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¤ñ¹ï

­ã´£³¡ªL

¦^´_ 50# Qin


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

¤U¸ü¦¸¼Æ: 10

2018-9-26 16:28

­ã´£³¡ªL

¦^´_ 53# Qin


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

­ã´£³¡ªL

¦^´_ 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)

¤U¸ü¦¸¼Æ: 3

2018-9-30 21:02

­ã´£³¡ªL

¦^´_ 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¶û³Â·Ð~~

­ã´£³¡ªL

¦^´_ 63# Qin


³æ¿W¤â°Ê¥´¶}dataÀÉ, ¬Ý­nªá¦h¤Ö®É¶¡???
¦pªGÀɮפ¤¦³«Ü¦h¤½¦¡, ¶}±Ò®É·|¦Û°Ê­«ºâ, ­nªá¨Ç®É¶¡ªº!

©Ò¿×[¤£¶}±Ò], ¹ê»Ú¬O¥Î§OºØ¤è¦¡¶}±Ò, ¥u¬O¦×²´¬Ý¤£¨ì,
¨S¦³¹ê»ÚÀÉ®×´ú¸Õ, ¤°»ò¤]»¡¤£·Ç!!!
_§Ú¥u¥Îoffice 2000, ©Ò¥H, ¥i¥t¦æµo©«, ½Ð¨ä¥L¤HÀ°¦£§a~~

­ã´£³¡ªL

¦^´_ 65# Qin

Sub Trans_Qty()
Dim R&
With Sheets("Qty on Hand")
     If .FilterMode Then .ShowAllData
     .UsedRange.Offset(1, 0).EntireRow.Delete
End With
R = Cells(Rows.Count, 1).End(xlUp).Row - 7
If R <= 0 Then Exit Sub
With ['Qty on Hand'!A2:I2].Resize(R)
     [A8:I8].Resize(R).Copy .Cells
     .Sort Key1:=.Item(6), Order1:=xlAscending, _
           Key2:=.Item(3), Order1:=xlAscending, Header:=xlNo
End With
With ['Qty on Hand'!I2].Resize(R)
     .Formula = "=IF(F2=F3,""A"",""B"")&TEXT(MID(I1,2,99),""0;-0;0;!0"")+N(H2)"
     .Value = .Value
     .Replace "A*", "", Lookat:=xlPart
     .Replace "B", ""
     .NumberFormatLocal = "#,##0;-#,##0"
End With
Application.Goto ['Qty on Hand'!A2]
End Sub

­ã´£³¡ªL

µy§ï
Sub Trans_Qty()
Dim R&
With Sheets("Qty on Hand")
     .AutoFilterMode = False
     .UsedRange.Offset(1, 0).EntireRow.Delete
End With
R = Cells(Rows.Count, 1).End(xlUp).Row - 7
If R <= 0 Then Exit Sub
With ['Qty on Hand'!A2:I2].Resize(R)
     [A8:I8].Resize(R).Copy .Cells
     .Sort Key1:=.Item(6), Order1:=xlAscending, _
           Key2:=.Item(3), Order1:=xlAscending, Header:=xlNo
End With
['Qty on Hand'!A1:I1].Resize(R + 1).AutoFilter
With ['Qty on Hand'!I2].Resize(R)
     .NumberFormatLocal = "#,##0;-#,##0"
     '.Formula = "=IF(F2=F3,""A"","""")&TEXT(MID(I1,2,99),""0;-0;0;!0"")+N(H2)" '¤½¦¡(1)
     '.Formula = "=IF(F2=F3,""A"","""")&IF(ROW(A1)=1,0,MID(I1,2,99))+N(H2)"    '¤½¦¡(2)
     .Formula = "=IF(F2=F3,"""",SUMIF(F:F,F2,H:H))"  '¤½¦¡(3)
     '¤TºØ¤½¦¡¥ô¿ï¤@­Ó, ¸ê®Æ¦h, ¬Ý­þ­Ó§Ö, ¿ï­þ­Ó
     .Value = .Value
     .Replace "A*", "", Lookat:=xlPart '¨Ï¥Î¤½¦¡(3), ¥i¬Ù²¤³o¤@¦æ
End With
Application.Goto ['Qty on Hand'!A2]
End Sub

­ã´£³¡ªL

¦^´_ 68# Qin

§ó¥¿¤U:
With ['Qty on Hand'!I2].Resize(R)
     .NumberFormatLocal = "#,##0;-#,##0"
     .Formula = "=IF(F2=F3,""A"","""")&TEXT(MID(I1,2,99),""0;-0;0;!0"")*(F2=F1)+N(H2)"  '¤½¦¡(1)
     '.Formula = "=IF(F2=F3,""A"","""")&IF(ROW(A1)=1,0,MID(I1,2,99))*(F2=F1)+N(H2)" '¤½¦¡(2)
     '.Formula = "=IF(F2=F3,"""",SUMIF(F:F,F2,H:H))" '¤½¦¡(3)
     .Value = .Value
     .Replace "A*", "", Lookat:=xlPart '¤½¦¡(1)¤Î(2), »Ý¥[³o¤@¦æ
End With