CODEWITHSUNDEEP
vbaexceldate
Vbasic4now
Vbasic4now

Reputation: 589

excel adding a few days to a date then auto populating a cell in another sheet with it

as said in the title I need a cell in another sheet to auto fill with the new date that has had a number of days added onto the current date, here is my code that I cant get to work:

Sub dateplus()

Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Sheet1")
Dim Ldate As Date

Ldate = dateadd("d", 2, "6/14/2016")

destSheet.range("A1").Value = Ldate

End Sub

I am not entirely familiar with this command of dateadd, what do I need to change in order for this code to do what I am trying to accomplish?

Thank you

Upvotes: 0

Views: 61

Answers (3)

B.G.
B.G.

Reputation: 6036

Added some comments to your code, hope this will help

Sub dateadd() ' Same name as a defined function, this will be a cause of Problems

Dim destSheet As Worksheet: Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") 'the variable sourceSheet is neither initialised nor used
Dim Ldate As Date

Ldate = dateadd("d", 2, "6/14/2016") 'the vba function you want to call is DateAdd, but here vba will call the sub dateadd because it has the same name. Will throw an Wrong Argument Count error.

destSheet.Range("A1").Value = Ldate

End Sub

Upvotes: 0

Ar4i
Ar4i

Reputation: 31

It seems that your destSheet isn't initialized, instead sourceSheet is. And to use DateAdd you need a Date object, not a string. It should look like this:

Sub CustomDateAdd()

Dim destSheet As Worksheet
Dim Ldate As Date

Set destSheet = ThisWorkbook.Worksheets("Sheet1")
Ldate = DateAdd("d", 2, Now)

destSheet.range("A1").Value = Format(Ldate,"MM/dd/yyyy")

End Sub

Why not use the Excel formula instead of calculating manually?

Sub CustomDateAdd()

Dim destSheet As Worksheet
Set destSheet = ThisWorkbook.Worksheets("Sheet1")

destSheet.range("A1").Formula = "=TODAY()+2"

End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

Option Explicit

Sub MyDateAdd()
    Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Sheet1")
    destSheet.Range("A1").Value = dateadd("d", 2, Date)
End Sub

Upvotes: 1

Related Questions