时间戳

首页 > 解决方案 > 跨多个工作簿的 VLOOKUP

excel - 跨多个工作簿的 VLOOKUP

问题描述

我基本上是在创建一个 VBA 脚本来清理报表。我把所有东西都打包了，但不知道如何VLOOKUP检查多个工作簿。我基本上必须将报告上的序列号与文件夹 A 或文件夹 B 上的序列号匹配。

截至目前，我Range().Value =VLOOKUP只为一个工作簿手动设置，但如果我这样做一秒钟，那么它可能会产生冲突。但是，我想知道在检查两个工作簿时是否可以从上到下VLOOKUP整列LastRow并忽略空白。

Sub LowTonerSOC()
Dim i As Long


For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
    If Cells(i, "J") <> "Yes" Then Rows(i).Delete
Next i

For i = Cells(Rows.Count, "K").End(xlUp).Row To 2 Step -1
    If Cells(i, "K") <> "Yes" Then Rows(i).Delete
Next i

For i = Cells(Rows.Count, "O").End(xlUp).Row To 2 Step -1
    If Cells(i, "O") <> "Headquarters" And Cells(i, "O") <> "INDUSTRIAL" And Cells(i, "O") <> "VAUSA" Then Rows(i).Delete
Next i

Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete

Range("C:F" & LastRow).Replace "", "999", xlWhole

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Cells(i, "C").Value2 >= 10 And Cells(i, "D").Value2 >= 10 And Cells(i, "E").Value2 >= 10 And Cells(i, "F").Value2 >= 10 Then
        Rows(i).Delete
    End If
Next i

For i = Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
    If Cells(i, "C").Value2 <= 10 Then
    Cells(i, "C").Interior.ColorIndex = 15
    End If
Next i

For i = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
    If Cells(i, "D").Value2 <= 10 Then
    Cells(i, "D").Interior.ColorIndex = 8
    End If
Next i

For i = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(i, "E").Value2 <= 10 Then
    Cells(i, "E").Interior.ColorIndex = 3
    End If
Next i

For i = Cells(Rows.Count, "F").End(xlUp).Row To 2 Step -1
    If Cells(i, "F").Value2 <= 10 Then
    Cells(i, "F").Interior.ColorIndex = 6
    End If
Next i

Application.Calculation = xlAutomatic
Range("C:F" & LastRow).Replace "999", "", xlWhole

ActiveSheet.Name = "Sheet1"

Range("G1").Value = "Physical Location"
Range("G2").Value = "=VLOOKUP(B2,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G3").Value = "=VLOOKUP(B3,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G4").Value = "=VLOOKUP(B4,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G5").Value = "=VLOOKUP(B5,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G6").Value = "=VLOOKUP(B6,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G7").Value = "=VLOOKUP(B7,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G8").Value = "=VLOOKUP(B8,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G9").Value = "=VLOOKUP(B9,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G10").Value = "=VLOOKUP(B10,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G11").Value = "=VLOOKUP(B11,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G12").Value = "=VLOOKUP(B12,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G13").Value = "=VLOOKUP(B13,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G14").Value = "=VLOOKUP(B14,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G15").Value = "=VLOOKUP(B15,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G16").Value = "=VLOOKUP(B16,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G17").Value = "=VLOOKUP(B17,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G18").Value = "=VLOOKUP(B18,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G19").Value = "=VLOOKUP(B19,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G20").Value = "=VLOOKUP(B20,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G21").Value = "=VLOOKUP(B21,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G22").Value = "=VLOOKUP(B22,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G23").Value = "=VLOOKUP(B23,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G24").Value = "=VLOOKUP(B24,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G25").Value = "=VLOOKUP(B25,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G26").Value = "=VLOOKUP(B26,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G27").Value = "=VLOOKUP(B27,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G28").Value = "=VLOOKUP(B28,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G29").Value = "=VLOOKUP(B29,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"
Range("G30").Value = "=VLOOKUP(B30,'\\spwsrf\Deskside\Desktop\EUS\AssestInventory\Printers\[Printers.xlsm]Sheet1'!$F$1:$G$230,2,0)"


Application.ScreenUpdating = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

标签： excelvba

解决方案

在将最后一行设置为整列中的 vlookup 时选择没有空白的列

LastRow = WorksheetFunction.Max(Sheets("Sheet1").Cells(Rows.Count, "E").End(xlUp).Row, 9)

然后对于具有多个工作簿的 vlookup，设置您的工作簿

Sub VlookMultipleWorkbooks()
Dim lookFor as String
Dim srchRange as Range
Dim book1 as Workbook
Dim book2 as Workbook

'Set some Workbook variables:
Set book1 = Workbooks("Book 1 Name") '<edit as needed
Set book2 = Workbooks("Book 2 Name") '<edit as needed

'Set a string variable that we will search for:
lookFor = book2.sheets(5).range(Cells(j, c + 1))

'Define the range to be searched in Book1.Sheets(4):
Set srchRange = book1.Sheets(4).Range(cells(row1+2,1).Address, cells(row2,col1).Address)

'This assumes that the Book2 is Open and you are on the desired active worksheet:
ActiveSheet.Cells(j, c + 2).value = _
     Application.WorksheetFunction.VLookup(lookFor, _
     book1.Sheets(4).Range(srchRange.Address), 3, False)

End Sub

请参阅此链接以获取具有多个vlookup 的vlookup Sub VlookupMultipleWorkbook

推荐阅读