使用VBA在excel中进行多页查找

先试试这个

我的查找公式为

=vlookup(表1!A1,表2!B:B,1,FALSE)

。

Sub randomstackmacro()
Dim output As String
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet

'This will apply the vba on cell C2 // Please modify this according to your requirements>
Range("C2").Select



Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
Set Sheet2 = ThisWorkbook.Sheets("Sheet2")
output = Application.WorksheetFunction.VLookup(Sheet1.Range("A3"), Sheet2.Range("A:B"), 2, False)

 'Output of Vlook up is set to the active cell as an example
ActiveCell.Formula = output

End Sub

输出

Sub lookuptest() 

Worksheets("CA").Range("A:A").Copy Worksheets("OUTPUT").Range("A:A") 


On Error Resume Next 
Dim cn_Row As Long 
Dim cn_Clm As Long 
Dim sheet1 As Worksheet 
Dim sheet2 As Worksheet 
Dim sheet3 As Worksheet 

Set sheet1 = ThisWorkbook.Sheets("CA") 
Set sheet2 = ThisWorkbook.Sheets("AT") 
Set sheet3 = ThisWorkbook.Sheets("OUTPUT") 

With sheet1 
sourcelastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 
'MsgBox "source file last row is: " & sourcelastrow 
End With 

Table1 = sheet1.Range("$A$2:$A$" & sourcelastrow) ' Employee_ID Column from Employee table 
Table2 = sheet2.Range("B:B") ' Range of Employee Table 1 
cn_Row = sheet3.Range("B2").Row ' Change E3 with the cell from where you need to start populating the Department 
cn_Clm = sheet3.Range("B2").Column 

For Each cl In Table1 
sheet3.Cells(cn_Row, cn_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False) 
cn_Row = cn_Row + 1 
Next cl 
'MsgBox "Done Lookup with Change Number" 

Dim id_row As Long 
Dim id_clm As Long 

Table3 = sheet1.Range("A:B") 
id_row = sheet3.Range("C2").Row 
id_clm = sheet3.Range("C2").Column 

For Each cl In Table1 
sheet3.Cells(id_row, id_clm) = Application.WorksheetFunction.VLookup(cl, Table3, 2, False) 
id_row = id_row + 1 
Next cl 

MsgBox "Done" 

End Sub