- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 7# owen9399
Shets("¿é¤J¸ê®Æ")- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim xM
- If Intersect(Target, Range("A2:A11")) Is Nothing Or Target(1) = "" Then Exit Sub
- xM = Application.Match(Target, [¤½¥q§Ç¸¹].Columns(1), 0)
- Target.Cells(1, 2) = [¤½¥q§Ç¸¹].Cells(xM, 2)
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- On Error Resume Next
- [§Ç¸¹].Validation.Delete
- If Intersect(Target, Range("A2:A11")) Is Nothing Then Exit Sub
- Range("Q2", [Q2].End(xlDown)).Resize(, 2).Name = "¤½¥q§Ç¸¹"
- Target.Name = "§Ç¸¹"
- With [§Ç¸¹].Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:="=" & [¤½¥q§Ç¸¹].Columns(1).Address
- End With
- End Sub
½Æ»s¥N½X ¥æ±b¸ê®Æ®w : ¤é´Áªº®æ¦¡ ½Ðקאּ "yyyy/mm/dd" ®æ¦¡- Sub «ö¶s3_Click()
- Dim Rng As Range, S As String, xi As Integer
- Dim Sh As Worksheet
- Set Sh = Sheets("¤é³øªí") ' ¤é³øªí
- Sh.Cells.Clear
- With Sheets("¥æ±b¸ê®Æ®w")
- If .AutoFilterMode Then .AutoFilterMode = False '¨ú®ø¿z¿ï
- .Range("a1").AutoFilter '[¦Û°Ê¿z¿ï] ¿z¿ï¥X¤@Ó²M³æ
- Set Rng = .AutoFilter.Range.Columns(6).Cells '[¦Û°Ê¿z¿ï]ªº²Ä6Äæ
- For xi = 2 To Rng.Count '³B¸Ì: ²Ä¤GÄæ ³æ¤¸®æ
- If InStr(S, "," & Rng(xi) & ",") = False Then 'Àˬd Àx¦s®æ ¬O§_¤w¥X²{¹L
- .Range("a1").AutoFilter Field:=6, Criteria1:=Rng(xi).Text '¨S¥X²{: «ü©w¬°¿z¿ïÈ
- S = S & "," & Rng(xi) & "," '¥[¤J¤w¥X²{¹Lªº¦r¦ê¤¤
- .UsedRange.SpecialCells(xlCellTypeVisible).Copy Sh.Cells(Rows.Count, "b").End(xlUp).Offset(2) '½Æ»s: ¸ê®Æªí¤¤¿z¿ï¥Xªº¸ê®Æ
- End If
- Next
- .AutoFilterMode = False '¨ú®ø¿z¿ï
- End With
- Sh.Activate
- End Sub
½Æ»s¥N½X |
|