返回列表 上一主題 發帖

[發問] 事件自動帶出(二個欄位)

[發問] 事件自動帶出(二個欄位)

問題:b&c欄key完後,自動帶出f&g欄

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B2:C65536").Address <> Union(Target, Range("B2:C65536")).Address Then Exit Sub
    Dim arr, brr(), i&, d As Object
    Set d = CreateObject("scripting.dictionary")
    arr = Sheets("name").UsedRange
    For i = 2 To UBound(arr)
         d(arr(i, 2) & Mid(arr(i, 4), 6, 1) & arr(i, 6)) = Array(arr(i, 7), arr(i, 8))
    Next
    arr = Cells(Target.Row, 2).Resize(Target.Rows.Count, 3)
    ReDim brr(1 To UBound(arr), 1 To 2)
    For i = 1 To UBound(arr)
        brr(i, 1) = d(arr(i, 1) & arr(i, 2))(0)
        brr(i, 2) = d(arr(i, 1) & arr(i, 2))(1)
    Next
    l = d.keys
    m = d.items
    Cells(Target.Row, 6).Resize(i - 1, 2) = brr
End Sub
目前只會一欄,二欄發現卡關,求解

TEST0705.rar (198.73 KB)

希望支持!

資料不多的話, 用公式:
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一次就跑一次迴圈, 也不是好辦法~~
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

當只有b欄,無c欄,帶不出資料
希望支持!

TOP

        靜思自在 : 口說好話、心想好意、身行好事。
返回列表 上一主題