vba 将范围内的每个单元格与另一个单元格进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15791138/
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
Comparing every cell in a range to another cell
提问by user2237763
I'm trying to write a macro that will look at a series of times, compare each one with a benchmark time and then generate a note next to each compared time. When I run the macro it will do this for the first cell in the range, but not for the rest.
我正在尝试编写一个宏来查看一系列时间，将每个时间与基准时间进行比较，然后在每个比较时间旁边生成一个注释。当我运行宏时，它将为范围内的第一个单元格执行此操作，但不会为其余单元格执行此操作。
Cell C8 in the "ActivityData" worksheet is the benchmark time. The times to be compared start in cell F12 of the "SIS" worksheet and vary in number.
“ActivityData”工作表中的单元格 C8 是基准时间。要比较的时间从“SIS”工作表的单元格 F12 开始，数量不同。
Here is the code in its entirety:
这是完整的代码：
Sub TimeCalc()
Dim wb As Workbook
Dim SIS As Worksheet
Dim Act As Worksheet
Set wb = ActiveWorkbook
Set SIS = wb.Worksheets("SIS")
Set Act = wb.Worksheets("ActivityData")
'Navigate to start times
wb.Worksheets("SIS").Select
Range("f12").Select
SIS.Range(Selection, Selection.End(xlDown)).Select
'Compare start times
For Each rng In Selection
If Act.Range("C8").Value < rng.Value Then
ActiveCell.Offset(0, -1).Range("A1").Value = "Missed " & Format(Act.Range("c8").Value, "Medium time") & " - " & Format((rng.Value - 0.000694444), "Medium Time")
Else
End If
On Error Resume Next
Next rng
End Sub
Thanks for your help!
谢谢你的帮助！
采纳答案by Taliesin
Assuming you want to place the result in the column to the left of the data you are examining:
假设您想将结果放在您正在检查的数据左侧的列中：
Option Explicit
Sub TimeCalc()
Dim wb As Workbook
Dim SIS As Worksheet
Dim Act As Worksheet
Dim myBaseLineRng As Range
Dim compareRng As Range
Dim Rng As Range
On Error GoTo 0
Set wb = ActiveWorkbook
Set SIS = wb.Worksheets("SIS")
Set Act = wb.Worksheets("ActivityData")
Set myBaseLineRng = Act.Range("C8")
Set compareRng = SIS.Range(SIS.Range("F12"), SIS.Range("F12").End(xlDown))
'Compare start times
For Each Rng In compareRng.Cells
If myBaseLineRng.Value < Rng.Value Then
Rng.Offset(0, -1).Value = "Missed " & Format(myBaseLineRng.Value, "Medium time") & " - " & Format((Rng.Value - 0.000694444), "Medium Time")
End If
Next Rng
End Sub
回答by David
You are only comparing the values in your benchmark list against the first value in your activity list as shown in the If statement. you will need to loop through your activity list to check each row if i understand your question right
您只是将基准列表中的值与活动列表中的第一个值进行比较，如 If 语句中所示。如果我理解您的问题，您将需要遍历您的活动列表以检查每一行
ie Loop through the activity list For each value in the activity list lOop through your benchmark times to check against that Next value in activity list.
即循环遍历活动列表对于活动列表中的每个值循环遍历您的基准时间以检查活动列表中的下一个值。
Unless I misunderstood your question.
除非我误解了你的问题。
