- ©«¤l
- 552
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 578
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-2-8
- ³Ì«áµn¿ý
- 2024-7-9
|
¥»©«³Ì«á¥Ñ 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×¥¿ì¨Ó§AnªºªF¦è¡A´N¹³§Ú»¡ªº¥unקï¨ä¤¤ªº§PÂ_´N¥i¥H¹F¨ì§Anªº¥Ø¼Ð¡A
ÁÙ¦³³o¸Ì¬O°Q½×°Ï¡A¤£¬O§Anµ{¦¡ªº¦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 |
|