比较 Excel VBA 中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7899034/
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
Compare values in Excel VBA
提问by venkat
I am trying to compare cell A1 with B1 and if it is true populate cell F1 with the A1 value. But irrespective of my input values the if condition becomes true.
我正在尝试将单元格 A1 与 B1 进行比较，如果它是真的，则用 A1 值填充单元格 F1。但无论我的输入值如何，if 条件都为真。
Sub Macro3()
Dim i As Integer
i = 1
For i = 1 To 10
If (Range("A" & i).Select = Range("B" & i).Select) Then
Range("A" & i).Select
Selection.Copy
Range("F" & i).Select
ActiveSheet.Paste
End If
Next i
End Sub
回答by CtrlDot
Instead of selecting, copying, and pasting, you can compare the Value property of the cells, then set the F column Value accordingly:
您可以比较单元格的 Value 属性，而不是选择、复制和粘贴，然后相应地设置 F 列值：
Dim i As Integer
For i = 1 To 10
If Range("A" & i).Value = Range("B" & i).Value Then
Range("F" & i).Value = Range("A" & i).Value
End If
Next
回答by aevanko
Consider this a compliment to Nick's answer (accept his if you find it to work, which you should). I wanted to help explain some of the things that are wrong in your code.
将此视为对尼克回答的赞美（如果您发现它有效，请接受他的回答，您应该这样做）。我想帮助解释您的代码中的一些错误。
Before FIX:
修复前：
Sub Macro3()
Dim i As Integer
i = 1
For i = 1 To 10
If (Range("A" & i).Select = Range("B" & i).Select) Then
Range("A" & i).Select
Selection.Copy
Range("F" & i).Select
ActiveSheet.Paste
End If
Next i
End Sub
AFTER FIX
修复后
Sub Macro4()
Dim i As Long
For i = 1 To 10
If Range("A" & i).Value = Range("B" & i).Value Then
Range("F" & i).Value = Range("A" & i).Value
End If
Next
End Sub
POINTS:
要点：
- Use Long instead of Integer (small optimization since VBA will convert the int to a long anyway)
- No need to declare i = 1 twice in a row
- You should be comparing values, not simply selecting cells. There is rarely, if ever, a need to use the .Select keyword. You can access all object's properties directly.
- Copy and paste is a heavy operation. Since you are in VBA, may as well just assign the value that is in A to the cell in column B. It's faster, and more effecient.
- 使用 Long 而不是 Integer （小的优化，因为 VBA 无论如何都会将 int 转换为 long）
- 无需连续两次声明 i = 1
- 您应该比较值，而不是简单地选择单元格。很少（如果有的话）需要使用 .Select 关键字。您可以直接访问所有对象的属性。
- 复制和粘贴是一项繁重的操作。由于您使用的是 VBA，不妨将 A 中的值分配给 B 列中的单元格。它更快，更有效。
I hope this helps. BTW, you can simple enter:
我希望这有帮助。顺便说一句，您可以简单地输入：
=IF(A1=B1,A1,"")
in F1 and drag the formula down to get a similar result.
在 F1 中向下拖动公式以获得类似的结果。
回答by brettdj
You can use a variant array to address your performance issue that you raise above. This code will run the same as Nicks except it will skip blanks cell, ie it will
您可以使用变体数组来解决您在上面提出的性能问题。此代码将与 Nicks 运行相同，但它会跳过空白单元格，即它会
- update the F value if A and B are the same
- skip updates if the A cell is blank
- leave the existing F values in place if A<>B
- 如果 A 和 B 相同，则更新 F 值
- 如果 A 单元格为空，则跳过更新
- 如果 A<>B，则保留现有的 F 值
It wasn't clear to me how you are comparing rows accross two sheets, can you expand on this?
我不清楚你如何比较两张纸上的行，你能扩展一下吗？
Sub MyArray()
Dim X As Variant
Dim Y As Variant
Dim lngrow As Long
X = Range([a1], Cells(Rows.Count, "B").End(xlUp))
Y = Range([f1], [f1].Offset(UBound(X, 1) - 1, 0))
For lngrow = 1 To UBound(X, 1)
If Len(X(lngrow, 1)) > 0 Then
If X(lngrow, 1) = X(lngrow, 2) Then Y(lngrow, 1) = X(lngrow, 1)
End If
Next
Range([f1], [f1].Offset(UBound(X, 1) - 1, 0)) = Y
End Sub
