有没有大神能帮忙写个跨表使用VLOOKUP功能的代码呢感谢

18720990993

要求在H列使用VLOOKUP 写入文件调价列表中的价格，请帮忙

3190496160

Sub jiage()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
f = Dir(ThisWorkbook.Path & "\调价列表 - 副本.xls*")
If f = "" Then MsgBox "找不到调价列表文件！": Exit Sub
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f, 0)
br = wb.Worksheets(1).[a1].CurrentRegion
For i = 2 To UBound(br)
    If Trim(br(i, 1)) <> "" Then
        d(Trim(br(i, 1))) = i
    End If
Next i
wb.Close False
With Sheets("null1")
    r = .Cells(Rows.Count, 1).End(xlUp).Row
    ar = .Range("a1:h" & r)
    For i = 2 To UBound(ar)
        If Trim(ar(i, 1)) <> "" Then
            m = d(Trim(ar(i, 1)))
            If m <> "" Then
                ar(i, 8) = br(m, 2)
            End If
        End If
    Next i
    .Range("a1:h" & r) = ar
End With
End Sub

3190496160

Desktop.zip (47.62 KB, 下载次数: 11)

2020-9-23 17:15 上传

点击文件名下载附件

f8b1987

1. Sub sql跨工作簿()
   
2.     t = Timer
   
3.     Dim cnn, mSQL, mFile, i&, j&
   
4.     Set conn = CreateObject("adodb.connection")
   
5.     Set Rst = CreateObject("ADODB.Recordset")
   
6.     Sheet1.Range("H:H").ClearContents
   
7.     PathStr = ThisWorkbook.Path & "\调价列表 - 副本.xls"
   
8.     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
   
9.     conn.Open strConn
   
10.     strSQL = "select count(SKU) as hj from [Sheet1$]"
    
11.     Set Rst = conn.Execute(strSQL)
    
12.     j = conn.Execute(strSQL).Fields("hj").Value + 1
    
13.     For i = Sheet1.Cells(Rows.Count, 1).End(3).Row To 2 Step -1
    
14.         strSQL = "select 保本价格 from [Sheet1$A1:B" & j & "] where SKU='" & Sheet1.Cells(i, 1) & "'"
    
15.         Sheet1.Cells(i, "H").CopyFromRecordset conn.Execute(strSQL)
    
16.     Next
    
17.     Rst.Close
    
18.     conn.Close
    
19.     Set conn = Nothing
    
20.     Set Rst = Nothing
    
21.     MsgBox Timer - t
    
22. End Sub

复制代码

f8b1987

SQL跨工作簿查询处理.zip (45.18 KB, 下载次数: 13)

2020-9-23 18:14 上传

点击文件名下载附件

18720990993

f8b1987 发表于 2020-9-23 18:13

中间有个报错conn.open strconn 这里 我也不是很懂

f8b1987

18720990993 发表于 2020-9-23 20:27
中间有个报错conn.open strconn 这里 我也不是很懂

我换了两台电脑都是正常的

18720990993

f8b1987 发表于 2020-9-23 22:20
我换了两台电脑都是正常的

可能是我电脑问题，感谢大佬