CODEWITHSUNDEEP
excelvba
TankTank
TankTank

Reputation: 83

Excel Macro date prompt for current date and changes the next cell 1 day ahead

How would I create an macro to prompt with current date and change the next cell 1 day ahead? I have a sample of what I have so far. Let me know if I am Close.

Sub Change_dates()
    Dim dtDate As Date
    dtDate = InputBox("Date", , Date)
    For Each cell In Intersect(Range("B " & dblRow).Value = dtDate, ActiveSheet.UsedRange)
        cell.Offset(0, 1).Select = cell.Offset(0, 1).Select + 1
    Next cell
End Sub

Upvotes: 0

Views: 4124

Answers (2)

John Bustos
John Bustos

Reputation: 19544

This should do exactly what you're looking for:

Sub Change_dates()
Dim dtDate As Date
Dim rng As Range
Dim FirstRow As Integer
   dtDate = InputBox("Date", , Date)
   Set rng = ActiveSheet.Columns("B:B").Find(What:=dtDate, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlNext)
   FirstRow = rng.Row
Do
   rng.Offset(0, 1).Value = rng.Offset(0, 1).Value + 1
   Set rng = ActiveSheet.Columns("B:B").FindNext(After:=rng)
Loop Until rng.Row = FirstRow

End Sub

Upvotes: 0

P. Stallworth
P. Stallworth

Reputation: 162

This code will prompt with current date, write to active cell, then write date + 1 to cell in the next column.

Sub Change_dates()
Dim dtDate As Date
dtDate = InputBox("Date", , Date)

ActiveCell.Value = dtDate
ActiveCell.Offset(0, 1).Value = dtDate + 1

End Sub

This will take the date from the prompt, write it in the selected rows in the range, then put the date + 1 in the rows one column to the right from the selected range.

Sub Change_dates_range()
Dim dtDate As Date
dtDate = InputBox("Date", , Date)

Set SelRange = Selection

For Each b In SelRange.Rows
    b.Value = dtDate
    b.Offset(0, 1).Value = dtDate + 1
Next

End Sub

If you instead want each row in the range to be + 1 day the row before, then I would increment dtDate at the end of the loop before the Next statement.

Upvotes: 1

Related Questions