Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address <> "$E$2" Then Exit Sub Dim rng As Range, WS1 As Worksheet, WS2 As Worksheet, Response, data Set WS1 = Sheets("BOM表1") Set WS2 = Sheets("BOM表2") Range("C2,B4:D1000,I4:I1000").ClearContents If Range("E2").Value > "" Then Response = MsgBox("按""是""查找BOM表1,按""否""查找BOM表2。", vbYesNo, "表格确认") If Response = vbYes Then Set rng = WS1.Columns("B").Find(what:=Range("E2").Value, lookat:=xlWhole) If Not rng Is Nothing Then data = WS1.Range("C" & rng.Row).Resize(rng.MergeArea.Count, 5) Range("C2") = rng.Offset(, -1).Value Else MsgBox "没有查找到相应的产品名称" Exit Sub End If Else Set rng = WS2.Columns("D").Find(what:=Range("E2").Value, lookat:=xlWhole) If Not rng Is Nothing Then Range("C2") = rng.Offset(, -2).Value Set rng = rng.Offset(1, -2) data = Application.WorksheetFunction.Transpose(WS2.Range(rng, rng.End(xlToRight).Offset(4))) Else MsgBox "没有查找到相应的产品名称" Exit Sub End If End If
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> "$E$2" Then Exit Sub
Dim rng As Range, WS1 As Worksheet, WS2 As Worksheet, Response, data
Set WS1 = Sheets("BOM表1")
Set WS2 = Sheets("BOM表2")
Range("C2,B4:D1000,I4:I1000").ClearContents
If Range("E2").Value > "" Then
Response = MsgBox("按""是""查找BOM表1,按""否""查找BOM表2。", vbYesNo, "表格确认")
If Response = vbYes Then
Set rng = WS1.Columns("B").Find(what:=Range("E2").Value, lookat:=xlWhole)
If Not rng Is Nothing Then
data = WS1.Range("C" & rng.Row).Resize(rng.MergeArea.Count, 5)
Range("C2") = rng.Offset(, -1).Value
Else
MsgBox "没有查找到相应的产品名称"
Exit Sub
End If
Else
Set rng = WS2.Columns("D").Find(what:=Range("E2").Value, lookat:=xlWhole)
If Not rng Is Nothing Then
Range("C2") = rng.Offset(, -2).Value
Set rng = rng.Offset(1, -2)
data = Application.WorksheetFunction.Transpose(WS2.Range(rng, rng.End(xlToRight).Offset(4)))
Else
MsgBox "没有查找到相应的产品名称"
Exit Sub
End If
End If
Range("B4").Resize(UBound(data)) = Application.Index(data, , 1)
Range("C4").Resize(UBound(data)) = Application.Index(data, , 2)
Range("D4").Resize(UBound(data)) = Application.Index(data, , 4)
Range("I4").Resize(UBound(data)) = Application.Index(data, , 5)
Else
MsgBox "产品名称不能为空值"
End If
Set rng = Nothing
Set WS1 = Nothing
Set WS2 = Nothing
End Sub