0

I am still new to vba and am writing a macro to calculate the time between two dates in two columns using dateDiff then print the difference in the cell next to it. i would also like do use a do until empty

Column A holds the start date Column B holds the end date Column C will hold the answer

i have some useful code but most of it is written to understand the goal of the macro and needs changed.

Range("A2").Select
Do Until IsEmpty(ActiveCell)
 For i = 2 To 25
  date1 = ["A" & i]
  date2 = ["B" & i]
  answer = DateDiff("n", date1, date2)
  "C" & i = answer
 next i
Loop

I appreciate any help!

Community
  • 1
  • 1
Peel
  • 31
  • 14

2 Answers2

1

A couple of things:

a. You can't have a variable when using the shorthand []:

[A1]

will work but 

i = 1
["A" & i]

Will not. So you must use.

Range("A" & i)

Or

Cells(i,1)

b. Your do loop does not change the ActiveCell. So it will never end if there is a value in A2

c. It is better practice to find the last row and use a FOR Loop. See here for many ways on finding the last row: Error in finding last used cell in VBA

d. Get in the habit now of declaring the parent of EVERY Range object, even if it is the ActiveSheet.

e. Always declare every variable, even if you declare it as Variant.

Dim i as Long
Dim lastRow as long
Dim date1 as double
Dim Date2 as double
Dim answer as long
With ActiveSheet
 lastRow = .Cells(.Rows.Count,1).End(xlUp).row
 For i = 2 To lastRow
  date1 = .Cells(i,1)
  date2 = .Cells(i,2)
  answer = DateDiff("n", date1, date2)
  .Cells(i,3) = answer
 next i
End With
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This is very helpful. when i use F8 to cycle through the for loop the answer variable is getting the correct answer but its not printing it into the corresponding row in column C. .Cells(i,3) = answer is this line suppossed to do that? – Peel Jun 08 '17 at 16:53
  • I forgot the `End With`. Try the edited code. it works for me as is, make sure you do not have a typo on the output variables. @Peel – Scott Craner Jun 08 '17 at 16:58
  • Thank you, that worked! I am also adding an if statement. there is a column "summary" and each cell either has yes or no. if the cell says no then i want this for loop to run and print in C. 'summary = .Cells (i, 4)' doesnt work do i add.value? – Peel Jun 08 '17 at 17:27
  • @Peel new question = new post. – Scott Craner Jun 08 '17 at 17:30
0

Without knowing the details of the format for dating, and assuming contiguous data, I would recommend the following:

Dim LR as Long
LR=Cells(.Rows.Count, "A").End(xlUp).Row
Sheets("NAME").Range("C1:C"&LR).Formula="=B1-A1"

Hopefully that helps!

Edited per Scott's comment.

Cyril
  • 6,448
  • 1
  • 18
  • 31
  • No need for the fill down: `Sheets("NAME").Range("C1:C"&LR).Formula="=B1-A1"` is sufficient for both lines. Excel will change the reference as needed. – Scott Craner Jun 08 '17 at 16:39
  • @ScottCraner thanks for that; I know I've had issues in the past, but I can't remember specifics and will trust you because... you're you. Lol, you've not steered me wrong to date! – Cyril Jun 08 '17 at 18:51