[已解决]怎样用VBA查找数据并填写Match offset?

ID莫乱改

 A列B列数据如图所示
我想在E1中输入物品名称，D1单元格就会自动填写出物品名称对应的编号

应问VBA代码怎么写？是不是要用到Match offset?
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

怎样用VBA查找数据并填写Match

[此贴子已经被作者于2009-12-23 10:42:00编辑过]

最佳答案

月排行榜 / 总排行榜

amulee

2009-12-23 10:46

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Count = 1 Then
        If Target.Column = 5 Then
            Dim arrYS, arrJG, iTemp, strTemp$
            arrYS = Application.Transpose(Range("B2:B" & Range("A65536").End(xlUp).Row))
            arrJG = Application.Transpose(Range("A2:A" & Range("A65536").End(xlUp).Row))
            iTemp = WorksheetFunction.Match(Target.Value, arrYS, 0)
            If Err.Number <> 0 Then
                strTemp = "#N/A!"
                Err.Clear
            Else
                strTemp = arrJG(iTemp)
            End If
            Cells(Target.Row, 4) = strTemp
        End If
    End If
End Sub

跳转到最佳答案楼层

amulee

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Count = 1 Then
        If Target.Column = 5 Then
            Dim arrYS, arrJG, iTemp, strTemp$
            arrYS = Application.Transpose(Range("B2:B" & Range("A65536").End(xlUp).Row))
            arrJG = Application.Transpose(Range("A2:A" & Range("A65536").End(xlUp).Row))
            iTemp = WorksheetFunction.Match(Target.Value, arrYS, 0)
            If Err.Number <> 0 Then
                strTemp = "#N/A!"
                Err.Clear
            Else
                strTemp = arrJG(iTemp)
            End If
            Cells(Target.Row, 4) = strTemp
        End If
    End If
End Sub