- 帖子
- 552
- 主題
- 3
- 精華
- 0
- 積分
- 578
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- office 2010
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2015-2-8
- 最後登錄
- 2024-2-26
  
|
本帖最後由 lpk187 於 2015-4-18 18:27 編輯
回復 11# maiko
要查看工作表事件的逐行"F8"鍵，須先在第一行執行中斷"F9"會比較容易觀察逐行執行時相關的區域變數視窗，
下面是我修改過的程式碼，我有用^^^^^^^標誌是修正原來你要的東西，就像我說的只要修改其中的判斷就可以達到你要的目標，
還有這裡是討論區，不是你要程式的地方，所以希望你可以從程式碼中學到東西！
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("工作表1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("工作表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("工作表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 |
|
發表於 2015-4-17 18:16
| 
