Question

请问，如何在2个excel中用vba来代替vlookup，求助

Answer 1

你果然又来提问了.那天我看到你的追问你竟然已经结贴了.郁闷
现在发给你两个文件的 两个文件必须是同目录的
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 2 Then
Dim yrng As Range
Workbooks.Open (ActiveWorkbook.Path & "\1.xls")
Workbooks("2.xls").Activate
Set yrng = Workbooks("1.xls").Sheets("sheet1").Range("B:B")
Dim rng1 As Range
aa = Target
Set rng1 = yrng.Find(aa, lookat:=xlWhole)
If Not rng1 Is Nothing Then
Target.Offset(0, 3) = rng1.Offset(0, 1)
End If
End If
Application.ScreenUpdating = True
End Sub

追问

求定制啊，我想套用进去还不行，晕了，你有百度HI吗？我加你了

追答

Sub 填写I列发货日期()
Application.ScreenUpdating = False
Dim yrng As Range
Dim af As Workbook
Dim yn As Boolean
endrow = Range("B65536").End(xlUp).Row
For Each af In Workbooks
bbbb = af.Name
If af.Name = "10年发货.xls" Then yn = True
Next
If yn = False Then Workbooks.Open (ActiveWorkbook.Path & "\" & "10年发货.xls")
Workbooks("西奥合同台账汇总表.xls").Activate
Set yrng = Workbooks("10年发货.xls").Sheets("sheet1").Range("B:B")
Dim rng1 As Range

For i = 3 To endrow
aa = Range("B" & i)
Set rng1 = yrng.Find(aa, lookat:=xlWhole)
If Not rng1 Is Nothing Then
Range("B" & i).Offset(0, 7).NumberFormat = "yyyy-m-d"
Range("B" & i).Offset(0, 7) = Format(rng1.Offset(0, 12), "yyyy-m-d")
End If
Next
Application.ScreenUpdating = True
End Sub

Answer 2

可以用 RANGE.FIND 方法
然后用.OFFSET 返回你要的值
具体可以看VBA里的帮助 事例就可以了