vba 在 Excel 中创建一个宏来比较两列，第三列中的答案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26302072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a macro in Excel that compares two columns, answers in third column
提问by bcwhite1618
I haven't found an appropriate answer for this question and I'm very new to VBA, so I hope someone will help me out.
我还没有找到这个问题的合适答案，而且我对 VBA 很陌生，所以我希望有人能帮助我。
I'm trying to create a Sub in my macro that does a simple value compare between two columns, row by row. If they are an exact match it will populate a third column with "Yes", otherwise it will say "No"
我试图在我的宏中创建一个 Sub ，它在两列之间逐行进行简单的值比较。如果它们完全匹配，它将用“是”填充第三列，否则它会说“否”
All columns are within an excel Table and have the same amount of rows, an example of what the result should look like is this (don't have enough rep to post image):
所有列都在一个 excel 表中并且具有相同数量的行，结果应该是什么样子的一个例子是这样的（没有足够的代表来发布图像）：
I was thinking something like a For Each statement but I'm not sure how to create it the right way. Thank you ahead of time for your help!
我在想像 For Each 语句这样的东西，但我不确定如何以正确的方式创建它。提前感谢您的帮助！
采纳答案by bcwhite1618
Thank you all for your input! I took elements from your answers and managed to come up with a code that solves my problem. Let me know if you have any questions
谢谢大家的意见！我从你的答案中提取了一些元素，并设法想出了一个代码来解决我的问题。如果您有任何问题，请告诉我
Sub Compare
On Error Resume Next
Dim Sm_Row As Long
Sm_Row = Range("Table1[Same?]").Row
For Each cl In Range("Table1[Same?]")
If Range("Table1[Col1]").Cells(Sm_Row, 1).Value = Range("Table1[Col2]").Cells(Sm_Row, 1).Value Then
cl.Value = "Yes"
Else
cl.Value = "No"
End If
Sm_Row = Sm_Row + 1
Next cl
End Sub
回答by JNevill
Quick subroutine to loop through rows 1 through 20 and compare results:
循环遍历第 1 行到第 20 行并比较结果的快速子例程：
for i = 1 to 20
If sheet1.range("A" & i).value = sheet1.Range("B" & i).value Then
sheet1.Range("C" & i).value = "No"
Else
sheet1.Range("C" & i).value = "Yes"
End if
Next i
Because this seems like more of a learning experiment, you can also reference cells by:
因为这更像是一个学习实验，您还可以通过以下方式引用单元格：
for i = 1 to 20
If sheet1.cells(i,1).value = sheet1.cells(i,2).value Then
sheet1.cells(i,3).value = "No"
Else
sheet1.cells(i,3).value = "Yes"
End if
Next i
You mention the range will vary in size. You can get the last row that is populated and then loop from 1 to that with:
您提到范围的大小会有所不同。您可以获得填充的最后一行，然后从 1 循环到：
Dim endRow as long
endRow = Sheet1.Range("A999999").End(xlUp).Row
for i = 1 to endRow
If sheet1.range("A" & i).value = sheet1.Range("B" & i).value Then
sheet1.Range("C" & i).value = "No"
Else
sheet1.Range("C" & i).value = "Yes"
End if
Next i
回答by StoriKnow
A table will automatically bring formulas to a new row when a new row is inserted. For instance, say you have the following table where the Same?column contains the formula =IF(C3=D3, "Yes", "No")
插入新行时，表格将自动将公式带到新行。例如，假设您有下表，其中该Same?列包含公式=IF(C3=D3, "Yes", "No")
As you enter a new row in the table, the formula in the Same?column will be automatically brought to the new row. For example, this is what that cell will look like once I hit Tabto create a new row:
当您在表格中输入新行时，列中的公式Same?将自动移至新行。例如，这是我点击Tab创建新行后该单元格的样子：
Now say you want to completely repopulate the table with a new set of data. That's no problem, simply copy the new data and paste it in the table like so:
现在假设您想用一组新数据完全重新填充表。这没问题，只需复制新数据并将其粘贴到表中，如下所示：
Copy
复制
Paste into first cell
粘贴到第一个单元格
The table takes care of the formulas for you, effectively making a macro unnecessary.
该表为您处理公式，有效地使宏变得不必要。
