在同一工作簿中的多个工作表中的Vlookup的VBA代码

Question

我对编码比较陌生，因此需要一些帮助。

我有多个工作表，其中在B列名称持有A列中&地址簿

我试图做到以下几点：

If worksheets("Input").Range("A1").Value 

存在于任何其他的工作表（A至Z）然后

worksheets("Input").Range("B1").Value = Cell.value 

接下来将匹配

细胞

worksheets("Input").Range("A1").Value 

i.e.-

if worksheet("B").Range("A1").Value = worksheets("Input").Range("A1").Value, 
    then worksheets("Input").Range("B1").Value = worksheet("B").Range("B1").Value 

谁能解释，而不必使用每个工作表了很多国际单项体育联合会的我怎么会在VBA做到这一点？

由于

安德鲁

Answer 1

我已用小例如具有一个“输入”片和另外两个片材（A和B在你情况下）测试了它的布局是这样的， ，，环路为vlookup开始次数输入板的列A的第二行...

Sub foo() 

Dim wks As Worksheet 
Dim wkb As Workbook 
Dim key As String 
Dim rowIndex As Integer 
Dim wksName As Variant 
Dim LastRow As Integer 


Set wkb = ThisWorkbook 

With wkb.Sheets("Input") 
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' totol number of entries in col A of input sheet 
End With 

' for each name in input sheet's column-A search the name in all sheets other then input sheet 
For rowIndex = 2 To LastRow Step 1 
    key = wkb.Worksheets("Input").Cells(rowIndex, 1).Value ' name to search 
    For Each wks In wkb.Worksheets ' loop through all the sheets 
     If Not wks.Name = "Input" Then ' avoid searching in input sheet itself 
      On Error Resume Next ' to search next sheet when not found in current one 
      wkb.Worksheets("Input").Cells(rowIndex, 2).Value = Application.WorksheetFunction.VLookup(key, wks.Range("A:B"), 2, False) 
     End If 
    Next wks 
Next rowIndex 

End Sub 

更新1>

Sub foo() 

Dim wks As Worksheet 
Dim wkb As Workbook 
Dim key As String 
Dim rowIndex As Integer 
Dim wksName As Variant 
Dim LastRow As Integer 


Set wkb = ThisWorkbook 



' for name in input sheet's A1 cell, search the name in all sheets other then input sheet 

    key = wkb.Worksheets("Input").Range("A1").Value ' name to search 
    For Each wks In wkb.Worksheets ' loop through all the sheets 
     If Not wks.Name = "Input" Then ' avoid searching in input sheet itself 
      On Error Resume Next ' to search next sheet when not found in current one 
      wkb.Worksheets("Input").Range("B1").Value = Application.WorksheetFunction.VLookup(key, wks.Range("A:B"), 2, False) 
     End If 
    Next wks 

End Sub