自动填充怎么弄呢

wxdrager

我有一个表 A 对应的是产品 和库位关系 ，，，









需求：随机 产品表，根据上表自动 填充对应的库位 谢谢  

咔咔乱坠

阐明需求，模拟结果，上传附件（表格）

zhangcheng6688

学习一下r = Sheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Row
arr = Sheets("sheet2").Range("a2:b" & r)
r1 = Sheets("sheet1").Cells(Rows.Count, "b").End(xlUp).Row
brr = Sheets("sheet1").Range("a2:b" & r1)
For i = 1 To UBound(arr)
    For j = 1 To UBound(brr)
        If arr(i, 1) = brr(j, 1) Then
           arr(i, 2) = brr(j, 2)
           GoTo x
        End If
     Next
     arr(i, 2) = "没有匹配数据"
x:
Next
Sheets("sheet2").[a2].Resize(UBound(arr), UBound(arr, 2)) = arr

zhangcheng6688

供参考！！！！！！

沈默00

写在公式，下拉，VBA也可以

jy03342543

Sub Test()
    Dim Rng As Range
    Dim Conn As Object, Rst As Object
    Dim StrConn As String, StrSQL As String
    Dim I As Integer, PathStr As String
    Set Conn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    PathStr = ThisWorkbook.FullName
    Select Case Application.Version * 1
    Case Is <= 11
        StrConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
    Case Is >= 12
        StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    End Select
    StrSQL = "SELECT A.产品,库位 From[Sheet1$E:F]A Left JOIN [Sheet1$A:B]B On A.产品 = B.产品"
    Conn.Open StrConn
    Set Rst = Conn.Execute(StrSQL)
    With Sheet1
        .Range("E2").CopyFromRecordset Rst
    End With
    Rst.Close
    Conn.Close
    Set Conn = Nothing
    Set Rst = Nothing
End Sub