DateAdd function in VBA

[Sake14]

Hello to all,

I am trying to add five hours to my date and time. I have together the time and date data in a cell in excel format.
I want to add five hours at each one of them and I am using the next formula but nothing happens ("H" is the column in which I have the Date and Time)
Any help please because I have to deliver that by tonight for my job:

Sub ForNextLoop()
Dim x As Integer
For x = 2 To Cells(2, "H").End(xlDown).Row - 1
Cells(x, "H") = DateAdd("h", 5, Cells(x, "H"))
Next
End Sub

Thanks a lot!!

 

[diddi]

hi and welcome to the board.
can I suggest you try stepping through your code (F8) and see how it is behaving.

 

[Norie]

The code works fine for me.

Have you checked the format of the cells and that the code is running on the correct sheet?

 

[Rick Rothstein]

Hello to all,

I am trying to add five hours to my date and time. I have together the time and date data in a cell in excel format.
I want to add five hours at each one of them and I am using the next formula but nothing happens ("H" is the column in which I have the Date and Time)
Any help please because I have to deliver that by tonight for my job:

Sub ForNextLoop()
Dim x As Integer
For x = 2 To Cells(2, "H").End(xlDown).Row - 1
Cells(x, "H") = DateAdd("h", 5, Cells(x, "H"))
Next
End Sub

Thanks a lot!!

Since your code works for me, the only thing I can conclude is your date/time value in the cells of Column H are not real or properly formed Excel date/times. That is where I think you should look to fix the problem. By the way, it looks like your loop will process all but the last row of data in Column H (because of the -1).

 

[Sake14]

Thanks for the reply as I am totally new to VBA I am trying with F8 but it doesn't show anything in the sheet, it runs only in the VBA windoe. How do I see the behavior of the code step by step?

 

[Sake14]

Thanks Rick!One qustion: in order to form column H (date and Time) I just added the time and date columns, after converting them with TimeValue and DateValue.Is this right?

 

[Sake14]

Thanks!! I was looking to the wrong sheet all the time. It works for me as well!

The code works fine for me.

Have you checked the format of the cells and that the code is running on the correct sheet?

 

[Norie]

The code will actually (sort of) work on cells with 'text' date/times.

 

[Rick Rothstein]

Thanks Rick!One qustion: in order to form column H (date and Time) I just added the time and date columns, after converting them with TimeValue and DateValue.Is this right?

If your original time and date were Text, then yes, that should be right... and I assume they were text because if they were real dates and times, you would have gotten a #VALUE! error when you used DateValue and TimeValue. So, if your date/times in Column H are real, then I am not sure what to tell you... as I said, your code worked fine for me. I am going to let one of the other volunteers pickup your question from here as I am going to sleep for the night now.

 

[Sake14]

Thank you all for your quick response and sorry comsuming your time, me not being careful!