如何比较两个不同的纸张并将值放在各自的位置？

I would like to compare two different cells within two different spreadsheets (One sheet is a base sheet) via VBA and if they are the same, take the values beside what they're being compared to and the place values in their respective positions.

我想通过VBA比较两个不同电子表格中的两个不同的单元格(一张是基础表格),如果它们是相同的,则将值与它们所比较的值以及它们各自位置的位置值相对应。

So in the picture, A is a match in both sheets so the value of 30 is copied over to the base sheet. The spreadsheets that I'm dealing with are not perfectly lined up like the image.

因此,在图片中,A是两个工作表中的匹配,因此将30的值复制到基础工作表。我正在处理的电子表格并不像图像那样完美排列。

Please let me know if you need more clarification. Thank you!

如果您需要更多说明,请与我们联系。谢谢!

2 个解决方案

#1

As I've commented, you can use VLOOKUP in this case.
Assuming both your values are in Column A to B of both sheet and A1 and B1 are headers, you can try below formula in Cell B2:

正如我评论的那样,在这种情况下你可以使用VLOOKUP。假设您的值都在两个工作表的A列到B列中,而A1和B1都是标题,您可以在单元格B2中尝试以下公式:

=VLOOKUP(A2,Sheet2!A:B,2,0)

which will give you the result above. It is also assumed that your sheet names are Sheet1 and Sheet2.

这会给你上面的结果。还假设您的工作表名称是Sheet1和Sheet2。

Edit: VBA Code

编辑:VBA代码

1. Using Formula Property

   使用公式属性

   With Sheets("Sheet1").Range("A2")
       .Formula = "=VLOOKUP(A2,Sheet2!A:B,2,0)"
       .Value = .Value
   End With
   

2. Using Evaluate Method

   使用评估方法

   With Sheets("Sheet1")
       ' 3 ways to use Evaluate method
       .Range("A2").Value = [VLOOKUP(A2,Sheet2!A:B,2,0)] ' Evaluate shortcut
       .Range("A2").Value = .Evaluate("VLOOKUP(A2,Sheet2!A:B,2,0)") ' explicitly
       .Range("A2").Value = Evaluate("VLOOKUP(A2,Sheet2!A:B,2,0)")
   End With
   

3. Using WorkSheetFunction Method - Already provided by Tom

   使用WorkSheetFunction方法 - 已由Tom提供

#2

You can use native sheet functions in VBA. Just prepend the worksheet function with 'Application.WorksheetFunction'.

您可以在VBA中使用本机表函数。只需在“Application.WorksheetFunction”前面加上工作表函数。

Application.WorksheetFunction.VLookup(Sheets("Sheet2").Range("A2"), Sheets("Sheet2").Columns("A:B"), 2, False)

Hope that helps.

希望有所帮助。