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

[µo°Ý] ®Ú¾Ú¦h±ø¥ó¿z¿ï²M³æ

[µo°Ý] ®Ú¾Ú¦h±ø¥ó¿z¿ï²M³æ



1. ®Ú¾Ú Shop ©M Code ¿z¿ï¦³Ãö Date ªº©Ò¦³²M³æ¡C
2. ¬d¥X¤F Date ªº©Ò¦³²M³æ«á¡A¿ï¾Ü¨ä¤¤¤@­Ó¤é´Á¦Ó§ä¥X Amount ª÷ÃB¡C
ÁÂÁ¡I


book1.rar (16.39 KB)

¦^´_  lpk187


    Á`¬O¨ì¤F³o¥y¡G             .Add Type:=xlValidateList, AlertStyle:=xlValidAler ...
maiko µoªí©ó 2015-4-22 05:33



³o­Ó°Q½×°ÏÀH«K§ä³£¦³§A­nªºµ{¦¡½X¡AÀH«K²Õ¤@²Õ³£¥i¥H¹F¨ì§A­nªº¥Ø¼Ð¡A¬Ý§A­n¤£­n¥Î¤ß¦Ó¤w¡I
ÁÙ¦³­Y¬Oµ{¦¡¤£¯à°õ¦æ¡A©Î¬O§A¦Û¤w¤£·|°£¿ù¡A³Ì¦n¤W¶Ç§Aªº­ìÀɮפW¨Ó¡A¦Û¦³¤HÀ°§A­×¥¿ªº¡I

TOP

¦^´_ 13# lpk187


    Á`¬O¨ì¤F³o¥y¡G             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & aa
´N¥X²{¿ù»~¡AµLªk¶i¦æ¡C

¤£¹LGoogle¤W¤F¸Ñ¤F¤@¨Ç¸ê®Æ¡A³ºµMµoı³£¨S¦³¤U©Ô¿ï³æ¤£­«½Æ­È¨Ã¥B±Æ§Ç³o¤è­±ªºµ{¦¡½X¡A¥u¦n¥Î¦Û¤vªº¤g¿ìªk¥h§¹¦¨³o­Ó¥\¯à¡A§Ú·Q³o¸Ì¤]À³¸Ó¨S¦³³o¤è­±ªº°Ñ¦Ò§a¡C

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-4-18 18:27 ½s¿è

¦^´_ 11# maiko

­n¬d¬Ý¤u§@ªí¨Æ¥óªº³v¦æ"F8"Áä¡A¶·¥ý¦b²Ä¤@¦æ°õ¦æ¤¤Â_"F9"·|¤ñ¸û®e©öÆ[¹î³v¦æ°õ¦æ®É¬ÛÃöªº°Ï°ìÅܼƵøµ¡¡A
¤U­±¬O§Ú­×§ï¹Lªºµ{¦¡½X¡A§Ú¦³¥Î^^^^^^^¼Ð»x¬O­×¥¿­ì¨Ó§A­nªºªF¦è¡A´N¹³§Ú»¡ªº¥u­n­×§ï¨ä¤¤ªº§PÂ_´N¥i¥H¹F¨ì§A­nªº¥Ø¼Ð¡A
ÁÙ¦³³o¸Ì¬O°Q½×°Ï¡A¤£¬O§A­nµ{¦¡ªº¦a¤è¡A©Ò¥H§Æ±æ§A¥i¥H±qµ{¦¡½X¤¤¾Ç¨ìªF¦è¡I

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [k5].Address Then
    '''''''''''''''''''''''''''''^^^^
        Range("B2:G" & Cells(Rows.Count, 2).End(xlUp).Row).AutoFilter
        ActiveWorkbook.Worksheets("¤u§@ªí1").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("¤u§@ªí1").AutoFilter.Sort.SortFields.Add Key:=Range( _
            "B2:B" & Cells(Rows.Count, 2).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("¤u§@ªí1").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("B2:G" & Cells(Rows.Count, 2).End(xlUp).Row).AutoFilter
        
    shop = [k3]
    Code = [k5]
'^^^^^^^^^^^^^^
    For Each Rng In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Rng = shop And Rng.Offset(0, 2) = Code Then
        '''''''''''''''''''''''''''''''''''^^^^^^^^^^^^^^^'
            K = K + 1
            If K = 1 Then
                Set Rn = Rng.Offset(0, 1)
            Else
                Set Rn = Union(Rn, Rng.Offset(0, 1))
            End If
        End If
    Next
    aa = Rn.Address
    If aa = "" Then
        Exit Sub
    Else
        With [L3].Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & aa
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = False
        End With
    End If
    [L3].Select
    '^^^^^^^^^^
    [L3] = "½Ð¿ï¾Ü¤é´Á"
   
End If
If Target.Address = [L3].Address Then
    If [L3] = "½Ð¿ï¾Ü¤é´Á" Then End
    For Each Rang In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Rang = [k3] And Rang.Offset(0, 1) = [L3] And Rang.Offset(0, 2) = [k5] Then
            [L5] = Rang.Offset(0, 5) '''''''''''''''''''''''''''''''''''''''''^^^^^^^^^^^^^^'
            End
        End If
    Next
End If
End Sub

TOP

Excel¼gªk¦p¹Ï¡A¥u­n¿z¿ïHÄæ´N¥i¥H¬Ý¨ì©Ò¦³¸ê®Æ¤F

TOP

¦^´_  maiko


    ¤£¦n·N«ä¡A»~·|§Aªº·N«ä¡A¤£¹L§A¤´¥i¥H­×§ï¨ä¤¤ªº§PÂ_´N¥i¥H¹F¨ì§A­nªºªF¦è¤F¡I
lpk187 µoªí©ó 2015-4-18 08:43



    ¤£¦n·N«ä¡A¯uªº¬O¯à¤O¦³­­¡A¤£ª¾¹D±q­þ¸Ì§ï¡A³Â·Ð§A½ç±Ð½ç±Ð¡A·PÁ¡I

TOP

¦^´_  maiko


    ¤£¦n·N«ä¡A»~·|§Aªº·N«ä¡A¤£¹L§A¤´¥i¥H­×§ï¨ä¤¤ªº§PÂ_´N¥i¥H¹F¨ì§A­nªºªF¦è¤F¡I
lpk187 µoªí©ó 2015-4-18 08:43



    ¦n§a¡A¸Õ¸Õ¬Ý¡A¦³¤£©ú¥Õ¦A°Ý§A¡A¦n¶Ü?

TOP

¦^´_ 8# maiko


    ¤£¦n·N«ä¡A»~·|§Aªº·N«ä¡A¤£¹L§A¤´¥i¥H­×§ï¨ä¤¤ªº§PÂ_´N¥i¥H¹F¨ì§A­nªºªF¦è¤F¡I

TOP

¦^´_  maiko
lpk187 µoªí©ó 2015-4-17 19:53



    ¤£¦n·N«ä¡A§Úªº­ì·N¬O¥ý¦³ Shop ©M Code ³o¨â­Ó±ø¥ó¡AµM«á¿z¿ï¥X Date¡A¦³¤F Date ¤§«á¤~¦³ Amount¡AÁÂÁ¡I

TOP

¦^´_ 4# maiko


    aaa.rar (21.21 KB)

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD