使用 VBA Excel 在两个不同单元格中添加/减去日期更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12958668/
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
Date Change with VBA Excel add/subtract in two different cells
提问by TankTank
How can I create a macro that will add a day in one cell and subtract a day in another cell at the same time? Here is what I have so far.
如何创建一个宏，在一个单元格中添加一天并同时在另一个单元格中减去一天？这是我到目前为止所拥有的。
Sub ChangeDates()
Dim cell As Range
For Each cell In Range("B:B")
cell.Value = cell.Value + 1
Next cell
For Each cell In Range("C:C")
cell.Value = cell.Value - 1
End Sub
采纳答案by John Bustos
Offset to the rescue!!
抵消救援！
Sub ChangeDates()
Dim cell As Range
For Each cell In Range("B:B")
cell.Value = cell.Value + 1
cell.offset(0,1).value = cell.offset(0,1).value - 1
Next cell
End Sub
Another thing you may consider is either looking at usedrange to not have to iterate through all of column B or put in a check to make sure the cells aren't blank... Just faster, better coding and stops you from having bad values where the cells were originally blank...
您可能会考虑的另一件事是查看 usedrange 不必遍历所有列 B 或进行检查以确保单元格不是空白...只是更快，更好的编码并阻止您在以下位置使用错误值单元格原本是空白的...
Sub ChangeDates()
Dim cell As Range
For Each cell In Intersect(Range("B:B"), ActiveSheet.UsedRange)
cell.Value = cell.Value + 1
cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - 1
Next cell
End Sub
回答by Scott Holtzman
I know you've accepted an answer, but I would like to offer this approach, which is even faster and more efficient than looping through all those cells.
我知道你已经接受了一个答案，但我想提供这种方法，它比循环遍历所有这些单元格更快、更有效。
If your dates are in Column A, then Column B will hold date +1and Column C will hold date -1
如果您的日期在 A 列中，则 B 列将保留date +1，C 列将保留date -1
Option Explicit
Sub ChangeDates()
Dim myRange As range
Dim mySheet As Worksheet
Set mySheet = Sheets("Sheet7") 'change to your sheet
With mySheet
Set myRange = .range("A1:A" & .range("A" & .Rows.Count).End(xlUp).Row)
myRange.Offset(, 1).FormulaR1C1 = "=RC[-1]+1"
myRange.Offset(, 2).FormulaR1C1 = "=RC[-2]-1"
End With
End Sub
