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

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

¥»©«³Ì«á¥Ñ Qin ©ó 2018-9-14 01:01 ½s¿è

¦^´_ 28# Kubi
   «u§r! ¸£³U¥d¦í¤F, §Ñ¤F¼Ò²Õ..
    ©¯¦n¥H¤W°ÝÃD¸Ñ¨M¤F.

    ¦b"temp"ªºÁôÂÃ¥\¯à

Data.rar (17.54 KB)

Search Data.rar (30.97 KB)

TOP

¥»©«³Ì«á¥Ñ Qin ©ó 2018-9-14 07:36 ½s¿è

©_©Ç, ¬°¦ó¯d¨¥¤£¯à§¹¥þÅã¥Ü???

TOP

¦^´_ 30# Qin

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
     If .Address = "$C$1" Then
        Cancel = True
        If [B1] = "" Then MsgBox "¥¼¿é¤J·j´M¤å¦r¡I ", 0 + 16: Exit Sub
        Call ·j´M(Array([B1], "", ""), Array(6, 7, 4))
        .Interior.ColorIndex = 6: [B1].Interior.ColorIndex = 6
     ElseIf .Address = "$C$2" Then
        Cancel = True
        If [B2] = "" Then MsgBox "¥¼¿é¤J·j´M¤å¦r¡I ", 0 + 16: Exit Sub
        Call ·j´M(Array("", [B2], ""), Array(6, 7, 4))
        .Interior.ColorIndex = 6: [B2].Interior.ColorIndex = 6
     ElseIf .Address = "$C$3" Then
        Cancel = True
        If [B3] = "" Then MsgBox "¥¼¿é¤J·j´M¤å¦r¡I ", 0 + 16: Exit Sub
        Call ·j´M(Array("", "", [B3]), Array(6, 7, 4))
        .Interior.ColorIndex = 6: [B3].Interior.ColorIndex = 6
     ElseIf .Address = "$A$1:$A$3" Then
        Cancel = True
        If [B1] & [B2] & [B3] = "" Then MsgBox "¥¼¿é¤J·j´M¤å¦r¡I ", 0 + 16: Exit Sub
        Call ·j´M(Array([B1], [B2], [B3]), Array(6, 7, 4))
        .Interior.ColorIndex = 6: [B1:B3].Interior.ColorIndex = 6
     End If
End With
End Sub

'¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
Sub ·j´M(Ur1, Ur2)
Dim Sht As Worksheet, xU As Range, xE As Range, k%
Call ²M°£
For Each Sht In Sheets
    If Left(Sht.Name, 4) <> "Data" Then GoTo 101
    If Sht.FilterMode Then Sht.ShowAllData
    Set xU = Sht.UsedRange
    For k = 0 To 2
        If Ur1(k) <> "" Then
           xU.AutoFilter Field:=Ur2(k), Criteria1:=Ur1(k)
        End If
    Next k
    Set xE = Cells(Rows.Count, 1).End(xlUp)(2)
    If xE.Row < 6 Then Set xE = [A6]
    xU.Offset(1, 0).Copy xE
    Sht.AutoFilterMode = False
101: Next
Set xE = Cells(Rows.Count, 1).End(xlUp)
If xE.Row < 6 Then MsgBox "§ä¤£¨ì²Å¦Xªº¸ê®Æ¡I ": Exit Sub
[A6:J6].Interior.ColorIndex = 35
[A7:J7].Interior.ColorIndex = 6
[A6:J7].Copy
Range(xE, [J6]).PasteSpecial Paste:=xlFormats
xE(2).EntireRow.Delete
[A6].Select
End Sub

Sub ²M°£()
With Sheets("Search")
     If .FilterMode Then .ShowAllData
     With .UsedRange.Offset(5, 0)
          .ClearContents
          .Interior.ColorIndex = xlNone
     End With
     .[A1,C1:C3].Interior.ColorIndex = 15
     .[B1:B3].Interior.ColorIndex = 35
     .[A6].Select
End With
End Sub
¡@
¡@

TOP

§ï¤@¤U[ÂùÀ»Ä²µo]³¡¥÷:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim T1$, T2$, T3$, R As Range, C%
With Target
     Select Case .Item(1).Address(0, 0)
       Case "C1": T1 = [B1]: Set R = [B1]: C = 1
       Case "C2": T2 = [B2]: Set R = [B2]: C = 1
       Case "C3": T3 = [B3]: Set R = [B3]: C = 1
       Case "A1": T1 = [B1]: T2 = [B2]: T3 = [B3]: Set R = [B1:B3]: C = 1
    End Select
    If C = 0 Then Exit Sub
    Cancel = True
    If T1 & T2 & T3 = "" Then MsgBox "¥¼¿é¤J·j´M¤å¦r¡I ", 0 + 16: Exit Sub
    Call ·j´M(Array(T1, T2, T3), Array(6, 7, 4))
    Union(.Cells, R).Interior.ColorIndex = 6
End With
End Sub

TOP

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

­ã¤jªº¤ßªk©M©Û¦¡¯uªº¤dÅܸU¤Æ,¼h¥X¤£½a.

Åý§Ú·Q°_«Ü¤[¥H«e, ¦b¦¹½×¾Â¤Wªº¤@¦ìª©¥D´¿»¡¹L:
¥u¦³§A·Q¤£¨ìªº, ¨S¦³°µ¤£¨ìªº.

TOP

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

¥»©«³Ì«á¥Ñ Qin ©ó 2018-9-14 13:18 ½s¿è

¦^´_ 28# Kubi

¤å±µ31¼Ó
§Ú·Q±N­ì¥»ªº¤@­ÓÀɤÀ¦¨2­ÓÀɨӨϥÎ.
AÀɬO: Data , Password 1234 , ¸ô®|¬O U:/ACWH/
BÀɬO: Search Data , Password 5678 , ¸ô®|¬O C:/Users/Public/Documents/
½Ð°Ý,­n°µ³o¨Ç­×§ï, »yªk¤S­n¦p¦ó¼g©O?
2ÀɬҪþ¤W
¦b31¼Ó

TOP

¦^´_ 36# Qin


SearchData01.rar (48.77 KB)

¥u¯à¥Î2003ª©Àɮ׮榡, ".xls" ¶·§ï¬° 2007ª©¥H¤Wªº°ÆÀɦW
¡eÀɮ׸ô®|¡f¦Û¦æ¥h­×§ï

TOP

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

­ã¤j, ÁÙ¦³¤@¨Ç°ÝÃD·Q­×§ï, ³Ò·Ð¤F!
1) Data Àɸê®Æ«ùÄò¼W¥[¤¤, ¦b¦³·s¸ê®Æ§ó·s¤~·|¥´¶}§ó·s (¦³¥Î±K½XÂê¤Wªº " 1234") , ©Ò¥H§Æ±æ¦b ¨Ï¥Î Search DataÀÉ·j´M¸ê®Æ®É,  Data ÀɬO¤£»Ý­n¥´¶}ªº
2) §Æ±æ·j´Mµ²ªG©Ò§e²{ªº "Date" ¬O¥Ñ²{¤µªº¦~¥÷¦Ü¸û¤[»·ªº¦~¥÷...
3) Á{®É·Q°_, ·Q¼W¥[1­Ó"¤é´Á"·j´M¥\¯à, ½Ð参¦Ò Search DataÀÉ

Search Data1.rar (38.27 KB)

TOP

¦^´_ 38# Qin

1) Data Àɸê®Æ«ùÄò¼W¥[¤¤, ¦b¦³·s¸ê®Æ§ó·s¤~·|¥´¶}§ó·s (¦³¥Î±K½XÂê¤Wªº " 1234") , ©Ò¥H§Æ±æ¦b ¨Ï¥Î Search DataÀÉ·j´M¸ê®Æ®É,  Data ÀɬO¤£»Ý­n¥´¶}ªº
¡@¡Ädata¬O¥H¡e°ßŪ¡f¶}±Òªº¡A¨Ã¤£¼vÅT­ìÀɦۤvªº¸ê®Æ
2) §Æ±æ·j´Mµ²ªG©Ò§e²{ªº "Date" ¬O¥Ñ²{¤µªº¦~¥÷¦Ü¸û¤[»·ªº¦~¥÷...
¡@¡Ä¤°»ò·N«ä¡H¡H¡H
3) Á{®É·Q°_, ·Q¼W¥[1­Ó"¤é´Á"·j´M¥\¯à, ½Ð参¦Ò Search DataÀÉ
¡@¡Ä¦pªG¤é´Á³£¤£¿é¤J¡A¦p¦ó¿z¡H
¡@¡Ä¤T±ø¥ó¤£¿é¤J¡A¥u¦³¤é´Á¡A¦p¦ó¿z¡H

TOP

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

½Ð°Ñ¦Ò

Q_1.png
2018-9-16 16:52
Q_2.png
2018-9-16 16:53
Q_3.png
2018-9-16 16:53

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD