CODEWITHSUNDEEP
excelvba
TankTank
TankTank

Reputation: 83

Date Change with VBA Excel add/subtract in two different cells

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

Upvotes: 1

Views: 6051

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

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 +1 and Column C will hold 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

Upvotes: 3

John Bustos
John Bustos

Reputation: 19544

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...

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

Upvotes: 0

Related Questions