CODEWITHSUNDEEP
excelvba
Peel
Peel

Reputation: 31

VBA - calculate and print answer for many rows

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!

Upvotes: 0

Views: 1030

Answers (2)

Cyril
Cyril

Reputation: 6829

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.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152465

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

Upvotes: 1

Related Questions