- 帖子
- 2798
- 主題
- 10
- 精華
- 0
- 積分
- 2854
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-4-20
|
資料不多的話, 用公式:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xA As Range, xB As Range
With Target
If .Count > 1 Or .Row = 1 Or .Column < 2 Or .Column > 3 Then Exit Sub
Set xA = .Cells(1, IIf(.Column = 2, 1, 0)): Set xB = xA(1, 2)
With Range(xA(1, 5), xA(1, 6))
If xA = "" Or xB = "" Then .ClearContents
.Formula = "=LOOKUP(,0/(name!$B$2:$B$999=" & xA.Address(0, 1) & ")/(name!$F$2:$F$999=" & xB.Address(0, 1) & "),name!G$2:G$999)"
.Value = .Value
.Replace "#N/A", "", Lookat:=xlWhole
End With
End With
End Sub
F2/公式:
=LOOKUP(,0/(name!$B$2:$B$999=$B2)/(name!$F$2:$F$999=$C2),name!G$2:G$999)
 TEST0705-v1.rar (14.66 KB)
=====================================
資料多, 每KEY一次就跑一次迴圈, 也不是好辦法~~ |
|
發表於 2019-7-6 11:58
| 